Handling Exceptional Behavior, Part II

Handle PL/SQL exceptions with best practices.

July/August 2003

In the May/June 2003 issue of Oracle Magazine, I offered suggestions for both an overall exception handling strategy and best practices for raising exceptions in your programs. In this article, I complete my treatment of error handling in PL/SQL, with a look at how best to handle exceptions once they have been raised.

For handling exceptions, there are two main considerations:

1. Deciding which errors should be handled and which can go unhandled in any given block of code.

2. Constructing reusable code elements that allow the handling (and logging) of errors in consistent, useful ways.

I touch on both of these topics in the following best-practice recommendations.

If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that, allowing for a graceful and informative failure.

The form this failure takes does not, by the way, necessarily need to be an exception. When writing functions, you may well decide that in the case of certain exceptions, you will want to return a value such as NULL, rather than allow an exception to propagate out of the function.

This recommendation is easy to demonstrate with the ubiquitous SELECT INTO lookup query. An error that often occurs is NO_DATA_FOUND , indicating that the query did not identify any rows. In the following function, book_title , I put my SELECT INTO inside a function, but I do not allow the NO_DATA_FOUND exception to propagate out of the function:

CREATE OR REPLACE FUNCTION book_title (
   isbn_in IN book.isbn%TYPE)
RETURN book.title%TYPE
IS
   l_title book.title%TYPE;
BEGIN
   SELECT title INTO l_title
     FROM book
     WHERE isbn =isbn_in;
     RETURN l_rec.title;
EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
     RETURN NULL;
END;   

In other words, if the ISBN passed to the function finds no book, return NULL for the title. This is an unambiguous indicator of failure; a book must have a title.

I have decided in this case not to allow NO_DATA_FOUND to propagate (unhandled) out of the function. I use a SELECT INTO (an implicit query) to fetch the book title; Oracle's implementation of implicit queries means that NO_DATA_FOUND (as well as TOO_MANY_ROWS ) might be raised. That doesn't mean, however, that within my function, it really is an exception when no row is found. In fact, I might be expecting to not find a match.

By returning NULL rather than propagating an exception, I leave it up to the users of my function to decide how they would like to deal with a "no row found" situation. They might raise an exception:

BEGIN
   l_title := book_title (l_isbn);
   IF l_title IS NULL
   THEN
      RAISE NO_DATA_FOUND;
   END IF;

or they might decide that such a result means that everything is, in fact, as expected:

BEGIN
   l_title := book_title (l_isbn);
   IF l_title IS NULL
   THEN
      process_new_book (l_isbn);
   END IF;

Your programs are better behaved and more likely to be useful and used if you take care of expected errors. In this case, if I let the exception propagate out, this function would be unpredictable and hard to integrate into my application, because exception handlers must be coded in the caller's code block.

The best way to achieve consistent, high-quality error handling throughout your application is to offer a set of predefined procedures that do all the basic plumbing of error handling, including the recording of error information (if desired), propagating the exception, and so on.

It is then crucial to make certain that development team members always use these procedures only in their WHEN clauses.

The following is the kind of code you should never write inside an exception handler:

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      v_msg := 'No company for id ' ||
         TO_CHAR (v_id);
      v_err := SQLCODE;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES
         (v_err, v_msg, v_prog,
            SYSDATE, USER);
   WHEN OTHERS
   THEN
      v_err := SQLCODE;
      v_msg := SQLERRM;
      v_prog := 'fixdebt';
      INSERT INTO errlog VALUES
         (v_err, v_msg, v_prog, SYSDATE,
            USER);
      RAISE;

At first glance, you might say, "Well, what's wrong with it? After all, it is self-documenting. I can look at the code and understand exactly what it is doing and how it gets the job done."

I strongly support the objective of self-documenting code, but in this case, the transparency of the code's intent is actually built on dangerous ground. This delicate structure will come tumbling down with any of the following developments:

• If you decide that you must add a column to the table, you'll have to change every INSERT statement.

• If you want to change the writing to a log table from the current strategy, which makes the error log part of the business transaction, what do you do? If you roll back the transaction, you'll lose the log. So, do you add savepoints and qualified rollback statements? Do you switch to writing errors out to a file? Both changes require substantial changes to the exception sections.

• If you change the naming convention for your programs, you'll have to update all hard-codings of the program names in the exception sections.

In essence, this sort of exception section makes sense only if you never make any mistakes, if you always get it right the first time, and if your code is perfect and frozen in time. That might be a tempting fantasy, but it will never be reality.

It is much better to strive for self-documenting code by hiding the implementation details, especially details involving the low-level "plumbing" of an application, such as exception handling and assertion logic. In the case of exception handling, the key is to move to using predefined, standardized error handler programs. Consider the following rewrite of the previous exception section:

EXCEPTION
WHEN NO_DATA_FOUND
THEN
errpkg.handle (
'No company for id ' ||
TO_CHAR (v_id),
log => TRUE,
reraise => FALSE);
WHEN OTHERS
THEN
errpkg.handle (
log => TRUE,
reraise => TRUE);

The code remains transparent in its meaning:

• If no data is found, this section will handle the error by logging the specified customized message but will not reraise the exception.

• For all other errors, this section logs the error and then reraises the exception.

Yet I have not hard-coded any specific implementation for either the logging or the reraising. Instead, I rely on the generic error handling package, errpkg, to do whatever is specified as the standard for my application. This approach does much more than increase the consistency and robustness of the error handling. It also dramatically improves programmer productivity. Developers can focus more on the application-specific logic and less on the mundane tasks that we all know must be done for any application.

Oracle allocates 1,000 error numbers, between -20,000 and -20,999, for application-specific errors (such as "Employee must be 18 years old" or "Reservation date must be in the future").

It is extremely important that you put controls around how developers work with these error numbers. You should define all error numbers and their associated messages in a database table or operating system file. Build (or generate) a package that gives names to these errors, and then raise the errors by using those names and not any hard-coded values.

Here is a fairly typical hard-coded, error-prone programming example using RAISE_APPLICATION_ERROR : Sam Developer is told to write a procedure to stop updates and inserts when an employee is younger than 18. Sam thinks, "Surely no one has used error 20734 yet, so I will use it" and produces the following code:

CREATE OR REPLACE PROCEDURE check_hiredate
(date_in IN DATE)
IS
BEGIN
IF date_in < ADD_MONTHS (
SYSDATE, -1 * 12 * 18)
THEN
RAISE_APPLICATION_ERROR (

-20734, 'Employee is too young.');
END IF;
END;

Check out all that hard-coding! And while Sam is writing his code, of course, Natasha Programmer also decides that 20734 is a fine error number.

Here is a much cleaner approach:

CREATE OR REPLACE PROCEDURE check_hiredate
(date_in IN DATE)
IS
BEGIN
IF emp_rules.emp_too_young (date_in)
THEN
errpkg.raise(
errnums.emp_too_young);
END IF;

END;

First, I have moved the logic defining a "too young" employee to a function; it is a fundamental best practice to put formulas and business rules inside functions. For error handling, Sam now simply knows that he calls the errpkg.raise procedure to raise his error. Which error? Sam goes to the list of predefined exceptions (either in documentation or via a GUI interface) and picks, by name, the one that matches.

How are these errors and error messages defined? The best way is to put them in a relational table, provide an interface to that table, and, most important, build a utility that generates all the associated code (exception, error number, and function). The accompanying download includes the following msginfo files that take care of all those tasks for you:

• msginfo.sql: creates a database table to hold message information, including error codes and messages

• msginfo.pkg: a package that provides access to the contents of the msginfo table and also generates a package of error numbers and exceptions from the contents of that table

• msginfo.fmb/fmx: a simple Oracle Forms screen for managing the contents of the msginfo table

When an error occurs in one of your PL/SQL blocks, it is often useful to be able to determine the values of persistent package variables at the time of the failure. You can do this to some extent with the debuggers available with many interactive development environments (IDEs). That approach does not, however, give you access to the data values within a user's application session.

One way to obtain this information is to write a "dump" procedure in each of your packages. This procedure displays or records the contents of any relevant variables or data structures—whatever you determine is of value inside that package. You can then feed this information to an error handler, to provide as much information as possible to the person debugging your code.

Providing such dump procedures can dramatically reduce the time spent inserting debug messages that need to be removed later as well as recording problems that appear intermittently and are hard to reproduce.

The dump procedure approach relies on conformance to preestablished standards, so method names and stack formats can be interpreted, but all of these details can be hidden from view in a package, such as the callstack package defined in the callstack.sql script file. This package keeps track of the call stack, by recording in a PL/SQL table each piece of code as it "announces" itself. The package then uses that stack to determine which dump methods need to be called when an error occurs.

Unfortunately, there is no reliable (and supported) way right now to easily determine which packages "have state" even if they aren't in the call stack, but this may be possible in the future. A straightforward exercise might be to extend this package to write to a log file or pipe, instead of just using the standard DBMS_OUTPUT package.

The following DEMO_PKG conforms to the dump API by including a procedure named instantiate_error_context in the specification:

CREATE OR REPLACE PACKAGE demo_pkg
IS
PROCEDURE proc1;
PROCEDURE instantiate_error_context;
END;
/

The following proc1 procedure sets the module name in the stack; assigns a value to a variable; and then calls proc2 , which also announces itself and modifies a package variable. It then, however, raises an exception.

PROCEDURE demo_pkg.proc1 IS
BEGIN
--announce entry into this module
errpkg.set_module_name(
'demo_pkg.proc1');

--application processing here
application.field_1 := 'test string';

proc2;

errpkg.remove_module_name;
EXCEPTION
WHEN OTHERS
THEN
errpkg.set_err_msg ('DAT023');
errpkg.raise_error (
'Failed Operation');
END;

The instantiation procedure passes the values of the package data (the package state) to the error package.

PROCEDURE demo_pkg
.instantiate_ error_context
IS
BEGIN
errpkg.add_context (
'DEMO_PKG', 'Field #1',
application.field_1);
errpkg.add_context (
'DEMO_PKG', 'Field #2',

application.field_2);
errpkg.add_context (
'DEMO_PKG', 'Field #3',
application.field_3);
END;

When I run demo_pkg.proc1 , I see the following output:

SQL> exec demo_pkg.proc1
Adding demo_pkg.proc1 to stack
Adding demo_pkg.proc2 to stack
Error Log Time: 13:15:33
Call Stack: demo_pkg.proc1 -->
demo_pkg.proc2
Comments: Failed Operation
CFRS Error No: DAT027
Oracle Error: ORA-01403: no data
found
----------DEMO_PKG----------------------
Field #1: test string
Field #2: -37
Field #3: NULL

Note that the errpkg used in the example and found in the callstack.sql file requires you to explicitly list the packages that contain instantiate_error_context procedures. An improved implementation would rely on dynamic SQL (either DBMS_SQL or native dynamic SQL) to automatically construct the program call and execute it.

Don't use WHEN OTHERS to grab any and every error. If you know that a certain exception might be raised, include a handler specifically for that.

Here is an exception section that clearly expects a DUP_VAL_ON_INDEX error to be raised but buries that information in WHEN OTHERS :

EXCEPTION
WHEN OTHERS
THEN
IF SQLCODE = -1
THEN
update_instead (...);
ELSE
errpkg.log;
RAISE;
END IF;

Here is a much better approach:

EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
update_instead (...);
WHEN OTHERS
THEN
errpkg.log;
RAISE;

By being as specific as possible, your code clearly states what you expect to have happen and how you want to handle your errors. That makes it easier to maintain and enhance. You can also more easily avoid hard-coding error numbers in your checks against SQLCODE .

When a developer designs a program well, a user of that program should never have to look at the body of code executed by that program to understand what it does and how it behaves. Yet the header of a program tells nothing about what errors it might raise and then send unhandled out of its exception section.

PL/SQL, unfortunately, doesn't offer a structured way to communicate this information. Consequently, you should consider creating your own conventions for including such news in the headers of your procedures and functions.

Suppose, for example, that I have built a package that calculates overdue fees on mortgages. The analyze_status procedure might raise the overdue.excessive_lateness or overdue.invalid_date_range exceptions. The count_by_borrower function could pass unhandled NO_DATA_FOUND or borrower.does_not_exist exceptions.

Here is a version of the package specification for overdue fees that makes explicit any error-raising behavior:

CREATE OR REPLACE PACKAGE overdue
IS
PROCEDURE analyze_status
(account_id_in IN account.id%TYPE)
/*
EXCEPTIONS RAISED:
borrower.excessive_lateness
syserr.invalid_date_range
*/
;

FUNCTION count_by_borrower
(borrower_id_in IN borrower.id%TYPE)
RETURN INTEGER
/*
EXCEPTIONS RAISED:
NO_DATA_FOUND
borrower.does_not_exist
*/
;

Notice that I use the same phrase "EXCEPTIONS RAISED" in both headers, so that it will be easy to search for this section in any of my programs. I also insert the comment between the header of the program (name and parameter list) and the closing semicolon for the statement. This placement ensures that the comment stays with the program.

Now, with a glance, any user of either of these programs understands that (a) she or he should include exceptions handlers for each of the documented exceptions and (b) a visit to the syserr (system-level error) or borrower packages will reveal more information about each of these user-defined exceptions.