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:
Can I handle the error condition in the current block, or should I punt to the caller?
If I cannot handle it locally, where should I declare the exception? In the local procedure? In ...