Learning Oracle PL/SQL

9.3. Exception-Handling Packages

Virtually all PL/SQL developers are going to have some decisions to make when they realize they need to define their own error conditions in an application. This section provides a package that I hope will ease the programming of packages in your applications. To begin with, I'll present two short code fragments. First, declaring, raising, and handling a local exception looks like this:

PROCEDURE wakeup
IS
   bad_hair_day EXCEPTION;
BEGIN
   ...
   IF overslept
   THEN
      RAISE bad_hair_day;
   END IF;
   ...
EXCEPTION
   WHEN bad_hair_day
   THEN
      NULL;
END;

Next, let's look at an example of calling a built-in procedure named RAISE_APPLICATION_ERROR, which raises an exception and gives it a numbered error code:

PROCEDURE wakeup
IS
BEGIN
   ...
   IF overslept THEN
      RAISE_APPLICATION_ERROR(-20392, 'Definitely a bad hair day');
   END IF;
END;

Here, -20392 is a programmer-defined error number between -20000 and -20999, and the text "Definitely a bad hair day" becomes available to the program called wakeup (for example, to display as the error message). A good reason to use RAISE_APPLICATION_ERROR is to associate an error number with a particular exception. Numbering the error vastly simplifies detecting it somewhere outside the procedure.

So, the decisions faced by the developer include the following:

1. Can I handle the error condition in the current block, or should I punt to the caller?

2. If I cannot handle it locally, where should I declare the exception? In the local procedure? In ...