Managing Exceptional Behavior, Part 1

Handle PL/SQL exceptions with best practices.

May/June 2003

Many programmers don't take the time to properly bulletproof their programs. Most of us find it hard enough—and more than enough work—to simply write the code that implements the positive aspects of an application: maintaining customers, generating invoices, and so on.

You write applications that often assume the best of all possible worlds, hoping that your programs are bug-free, that users will enter only the correct data in only the correct fashion, and that all systems (hardware and software) will always be a "go."

No matter how hard you try, there will always be one more bug in your application. And your users will always find just the right sequence of keystrokes it takes to make a screen implode. The reality is clear: Either you spend the time up front to properly debug and bulletproof your programs, or you will fight an unending series of rear-guard battles, taking frantic calls from your users and putting out the fires.

Fortunately, PL/SQL offers a powerful and flexible way to trap and handle errors in your programs. It is entirely feasible within the PL/SQL language to build an application that fully protects the user and the database from errors. And if you go about it correctly, you minimize the amount of code you need to write to get the job done.

This article, the first of two parts, will explore a set of best practices you should follow to incorporate high-quality, comprehensive error handling in your PL/SQL-based applications. This article will start by considering some overall best practices for error handling and then focus on best practices relating to raising exceptions. The next article will move on to how best to handle exceptions.

Before you start writing any application code, you first need to decide on your overall strategy for error handling, as well as specific coding guidelines. There is no single strategy that works for all applications; you have to decide what makes the most sense for your particular system. You may determine, for example, that all stored procedures should have an exception section so that errors are trapped and handled as close as possible to the block in which they were raised. You might, on the other hand, decide that you want an exception section only at the outer-most block.

Once you have set the overall approach, you need to face up to the challenge of getting all developers on the team to write their error handling in the same way. If you do not follow consistent approaches to raising, handling, and logging errors, users will be terribly confused, and people supporting the application will have a very tough time figuring out what is going wrong.

The best way to implement consistent, applicationwide error handling is to build and use a standardized package that contains at least the following elements:

• A raise program that hides the complexity of RAISE_APPLICATION_ERROR and application-specific error numbers

• Procedures that perform most exception-handling tasks, such as writing to an error log and handling errors

• A function that returns error message text for a given error number

Here is the specification of a simple error-handling package that conforms to the above requirements (and is located in the errpkg.pkg file):

CREATE OR REPLACE PACKAGE errpkg
IS
   PROCEDURE raise (
      err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL);
   PROCEDURE report_and_stop (
      err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL);
   PROCEDURE report_and_go (
      err_in IN INTEGER := SQLCODE,
      msg_in IN VARCHAR2 := NULL);
   FUNCTION errtext (
      err_in IN INTEGER := SQLCODE)
      RETURN VARCHAR2;
END errpkg;

I will take a look at how to implement the procedures and functions of this error-handling package (errpkg.pkg), and the impact they have on your code.

Exceptions can't be handled until they are raised, so I'll begin by looking at some best practices for raising exceptions. The following best practices describe how to check for conditions that might require the raising of an exception, how to decide what exception information to propagate, and how best to raise exceptions.

Validate Preconditions

Every time you write a program, you make assumptions. Users of your program don't necessarily know about those assumptions. If you do not "code defensively" and make sure that your assumptions are not violated, your programs can break down in unpredictable ways.

Use assertion routines to make it easy to validate assumptions in a declarative fashion. These routines, standardized for an entire application, take care of all the housekeeping. They describe what to do when a condition fails, how to report the problem, and whether and how to stop the program from continuing.

Listing 1 contains a simple assertion program that checks to see if a condition is TRUE. If the condition is instead FALSE or NULL, the procedure displays a message to the screen and then optionally raises an exception with dynamic PL/SQL.

On lines 2 through 6 of Listing 1, I create a parameter list that allows for significant flexibility, a key aspect of reusable code. First, I supply the condition that I wish to assert. If the condition (a Boolean expression) evaluates to TRUE, then the assertion program does nothing. If the expression evaluates to FALSE or NULL, the assertion procedure then relies on the other parameters to display the error. I further specify that I want an exception raised (when raise_exception_in is TRUE, the default). The procedure will use native dynamic SQL (lines 18 through 19) to raise whatever exception I name in the fourth parameter (lines 5 through 6).

With the assertion procedure in place, I can in a declarative fashion make sure that all inputs are valid before proceeding with my business logic. For example:

BEGIN
  assert (isbn_in IS NOT NULL,
    'The ISBN must be provided.');
  assert (page_count_in < 2000,
    'Readers don't like big, fat books!');

Try setting up a block template that breaks up your executable section into a number of "virtual" sections, as follows:

CREATE OR REPLACE PROCEDURE <name>
IS
   <declarations>
   PROCEDURE initialize IS
   BEGIN
      <any startup code>
   END;
   PROCEDURE assert IS
   BEGIN
      <sequence of assertions>
   END;
   PROCEDURE cleanup IS
   BEGIN
      <any clean-up code>
   END;
BEGIN
   initialize;
   assert;
   <body of code>
   cleanup;
END <name>;

Prototypes for assertion procedures like those shown here are located in the assert.pro and assert.pkg files.

If you are working with a programming language that does not have a sophisticated error-handling architecture (unlike PL/SQL or Java), you may have gotten used to adding OUT parameters to every program that passes back status code and messages.

Don't do this in PL/SQL, unless you need to pass back such information to the host environment that is calling the PL/SQL code. If your PL/SQL programs are communicating with and being called from other PL/SQL blocks, you should rely on the default model: raise exceptions and handle those exceptions in separate exception sections of your blocks.

Here is an example of code to avoid:

BEGIN
   overdue.analyze_status (
      title_in,
      start_date_in,
      report_info_out,
      error_code,
      error_msg);
   IF error_code != 0
   THEN
      errpkg.log (...);
      GOTO end_of_program;
   END IF;
   overdue.send_report (
      report_info_out,
      error_code,
      error_msg);
   IF error_code != 0
   THEN
      err.log (...);
      GOTO end_of_program;
   END IF;
   ...
   <end_of_program>
   NULL;
END;

Notice that I must check the status of each subprogram call; I also usually end up using GOTO s and labels to respond to the occurrence of failures. Finally, I all too often will hard-code values such as the "0" as an indication of success. What happens if I ever change the success indicator?

Using "traditional" PL/SQL logic, the above executable section would look something like this:

BEGIN
   overdue.analyze_status (
      title_in,
      start_date_in,
      report_info_out);
   overdue.send_report (report_info_out);
EXCEPTION
   WHEN overdue.invalid_date
   THEN
      errpkg.report_and_go (msg_in => start_date_in);
   WHEN OTHERS
   THEN
      errpkg.report_and_stop;
END; 

When conforming to the default exception-handling model of PL/SQL, executable sections are clean, simple, and easy to follow. You don't have to check for status after every program call. You simply include an exception section to trap and deal with crises as they arise.

If you are raising a "system" exception such as NO_DATA_FOUND , you use RAISE . If you want to raise an application-specific error, you use RAISE_APPLICATION_ERROR . If you use the latter, you then have to provide an error number and message. This leads to unnecessary and damaging hard-coding.

A better approach is to provide a predefined raise procedure that automatically checks the error number and determines the correct way to raise the error. An example of such a procedure is in the errpkg.pkg file and is described below.

Instead of writing this:

RAISE_APPLICATION_ERROR (
   -20734,
   'Employee must be 18 years old.');

you should write this:

errpkg.raise (errnums.en_emp_too_young);

Notice that in this second implementation, I no longer hard-code the error number (which I most likely "picked out of a hat" and assumed that no one else would ever possibly want to use) or the error message. Instead, I open up the errnums package, which contains a set of predefined error numbers, find the one that matches my situation, and then reference the error by its named constant. In other words, the errnums package specification might look something like this (and can be found in errnums.pkg):

CREATE OR REPLACE PACKAGE errnums
IS
   exc_bal_too_low EXCEPTION;
   en_bal_too_low CONSTANT INTEGER := -20100;
   PRAGMA EXCEPTION_INIT (exc_bal_too_low, -20100);
   exc_emp_too_young EXCEPTION;
   en_emp_too_young CONSTANT INTEGER := -20200;
   PRAGMA EXCEPTION_INIT (exc_emp_too_young, -20200);
END errnums;

Listing 2 contains the implementation of the errpkg.raise procedure. Here is an explanation of the most interesting elements of the program: Line 2. I pass in the following: an error number, the default SQLCODE , and an override error message. If none is provided, I use the default message stored in a messages table. Lines 5 through 7. I provide the range of error numbers to which RAISE_ APPLICATION_ERROR applies. If the supplied error number falls in that range, I call the built-in procedure using the supplied error number and message. If the message is null, I use the errpkg.errtext function. Lines 9 through 11. I also handle positive numbers for application-specific error numbers. By handling positive error message numbers, I am not constrained to error numbers between -20,999 and -20,000, some of which Oracle also uses (although I stay away from 1 and 100, the only two positive error numbers that Oracle does use). Lines 14 through 18. I construct a PL/SQL block that declares a local exception, uses the pragma EXCEPTION_INIT to associate that exception with the supplied error number, and then raises that exception.

Using this errpkg.raise procedure, individual developers do not have to make judgment calls about how they should raise the exception (using RAISE or RAISE_APPLICATION_ERROR ). They simply pass the appropriate error number (hopefully identified via a named constant) and let the RAISE engine do all the heavy lifting.

You should never use RAISE to abort normal processing in a program and go to the appropriate WHEN handler. You should raise an exception only when an error has occurred; do not raise exceptions to control program flow.

The function in Listing 3 demonstrates the problem; it performs a full table scan of a collection and immediately exits when it finds a match. The exit_function exception is used to abort the function if the input title is NULL; it is also used as the last line in the function.

In Listing 3, I manage to make it all the way to the end of the function, and then I am thwarted by having an exception raised. This is very poorly structured code: it's hard to understand and hard to maintain.

Be on the lookout for a symptom of this misuse of error handling: declared exceptions whose names describe actions ("exit function"). The name of an exception should describe an error situation, such as "null name" or "invalid date."

A better approach is contained in Listing 4.

I've shown how you can take a much more organized and robust approach to raising exceptions: first, write a general exception-handling package that collects all the tools an application team needs for consistent error management. Second, put into place clear guidelines for raising exceptions, backed up by a generic raise program that takes the guesswork out of an individual developer's efforts.

In my next article, I will look at the best practices for handling exceptions that have been raised.