Error Handling with Implicit Cursors

The implicit cursor version of the SELECT statement is kind of a black box. You pass the SQL statement to the SQL layer in the database, and it returns a single row of information. You can't get inside the separate operations of the cursor, such as the open, fetch, and close stages. You are also stuck with the fact that Oracle will automatically raise exceptions from within the implicit SELECT for two common outcomes:

· The query does not find any rows matching your criteria. In this case, Oracle raises the NO_DATA_FOUND exception.

· The SELECT statement returns more than one row. In this case, Oracle raises the TOO_ MANY_ROWS exception.

When either of these scenarios occurs (as well as any other exceptions raised when executing a SQL statement), execution of the current block terminates and control is passed to the exception section. You have no control over this process flow; you cannot tell Oracle that with this implicit cursor, you actually expect to not find any rows and it is not an error. Instead, whenever you code an implicit cursor, you should include an exception section that traps and handles these two exceptions (and perhaps others, depending on your application logic).

In the following block of code, I query the title of a book based on its ISBN number, but I also anticipate the possible problems that arise:

DECLARE l_isbn book.isbn%TYPE := '0-596-00121-5'; l_title book.title%TYPE;BEGIN SELECT title INTO l_title FROM book WHERE isbn = l_isbn;EXCEPTION WHEN NO_DATA_FOUND THEN display.line ('Unknown book: ' || l_isbn); WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop ('Data integrity error for: ' || l_isbn); RAISE;END;

One of the problems with using implicit queries is that there is an awesome temptation to make assumptions about the data being retrieved, such as:

· "There can never possibly be more than one row in the book table for a given ISBN; we have constraints in place to guarantee that."

· "There will always be an entry in the book table for Steven and Bill's Oracle PL/SQL Programming. I don't have to worry about NO_DATA_FOUND."

The consequence of such assumptions is often that we neglect to include exception handlers for our implicit queries.

Now, it may well be true that today, with the current set of data, a query will return only a single row. If the nature of the data ever changes, however, you may find that the SELECT statement that formerly identified a single row now returns several. Your program will raise an exception, the exception will not be properly handled, and this could cause problems in your code.

You should, as a rule, always include handlers for NO_DATA_FOUND and TOO_MANY_ROWS whenever you write an implicit query. More generally, you should include error handlers for any errors that you can reasonably anticipate will occur in your program. The action you take when an error does arise will vary. Consider the code that retrieves a book title for an ISBN. Implemented below as a function, notice that my two error handlers act very differently: NO_DATA_FOUND returns a value, while TOO_MANY_ROWS logs the error and re-raises the exception, causing the function to actually fail. (See Chapter 6 for more information about the errpkg package.)

CREATE OR REPLACE FUNCTION book_title ( isbn_in IN book.isbn%TYPE) RETURN book.title%TYPEIS return_value book.title%TYPE;BEGIN SELECT title INTO return_value FROM book WHERE isbn = l_isbn; RETURN return_value;EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; WHEN TOO_MANY_ROWS THEN errpkg.record_and_stop ( 'Data integrity error for: ' || l_isbn); RAISE;END;

Here is the reasoning behind these varied treatments: the point of my function is to return the name of a book, which can never be NULL. The function can also be used to validate an ISBN (i.e., "does a book exist for this ISBN?"). For this reason, I really don't want my function to raise an exception when no book is found for an ISBN; that may actually constitute a successful condition, depending on how the function is being used. The logic may be, "If a book does not exist with this ISBN, then it can be used for a new book," which might be coded as:

IF book_title ('0-596-00121-7') IS NULLTHEN ...

In other words, the fact that no book exists for that ISBN is not an error and should not be treated as one within my general lookup function.

On the other hand, if the query raises the TOO_MANY_ROWS exception, I have a real problem: there should never be two different books with the same ISBN number. So in this case, I need to log the error and then stop the application from continuing.