O'Reilly logo

Learning Oracle PL/SQL by Steven Feuerstein, Bill Pribyl

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required