6.3.1.1 Raising exceptions in nested blocks

When you declare an exception in a block, it is local to that block, but global to all the blocks enclosed by that block (nested blocks). In the version of check_account shown in the following example, the procedure contains an anonymous sub-block that also raises the overdue_balance. Because the sub-block is enclosed by the procedure block, PL/SQL can resolve the reference to that exception:

PROCEDURE check_account (company_id_in IN NUMBER)

IS

overdue_balance EXCEPTION;

BEGIN

... executable statements ...

-- Start of sub-block inside check_account

BEGIN

... statements within sub-block ...

RAISE overdue_balance; -- Exception raised in sub-block.

END;

-- End of sub-block inside check_account

LOOP

...

IF ... THEN

RAISE overdue_balance; -- Exception raised in main block.

END IF;

END LOOP;

EXCEPTION

WHEN overdue_balance THEN ... -- Exception handled in main block.

END;

When the overdue_balance exception is raised in either the sub-block or the main block, control is transferred immediately to the main block—the only exception section in the entire procedure. Because overdue_balance was declared for the whole procedure, the name is known throughout all sub-blocks.

6.3.2 Using raise_application_error

Oracle provides the RAISE_APPLICATION_ERROR procedure (defined in the default DBMS_STANDARD package) to raise application-specific errors in your application. The advantage to using RAISE_APPLICATION_ERROR instead of RAISE (which can also raise an application-specific, explicitly declared exception) is that you can associate an error message with the exception.

When this procedure is run, execution of the current PL/SQL block halts immediately, and any changes made to OUT or IN OUT arguments (if present) will be reversed. Changes made to global data structures, such as packaged variables, and to database objects (by executing an INSERT, UPDATE, or DELETE) will not be rolled back. You must execute an explicit ROLLBACK in your exception section to reverse the effect of DML operations.

The header for this procedure (defined in package DBMS_STANDARD) is shown here:

PROCEDURE RAISE_APPLICATION_ERROR (

num binary_integer,

msg varchar2,

keeperrorstack boolean default FALSE);

where num is the error number and must be a value between -20,999 and -20,000 (just think: Oracle needs all the rest of those negative integers for its own exceptions!); msg is the error message and must be no more than 2K characters in length (any text beyond that limit will be ignored); and keeperrorstack indicates whether you want to add the error to any already on the stack (TRUE) or replace the existing errors (the default, FALSE).

Oracle is supposed to have set aside the range of -20,999 and -20,000 for use by its customers, but be warned: several built-in packages, including DBMS_OUTPUT and DBMS_DESCRIBE, use error numbers -20,000 through -20,005. This is very rude, but they do it. See the package documentation for descriptions of usage.

Let's take a look at one useful application of this built-in. Suppose that I need to support error messages in different languages for my user community. I create a separate error_table to store all these messages, segregated by the string_language value. I then create a procedure to raise the specified error, grabbing the appropriate error message from the table based on the NLS_LANGUAGE parameter value:

/* File on web: raise_by_language.sp */

CREATE OR REPLACE PROCEDURE raise_by_language (code_in IN PLS_INTEGER)

IS

l_message error_table.error_string;

BEGIN

SELECT error_string

INTO l_message

FROM error_table, v$nls_parameters v

WHERE error_number = code_in

AND string_language = v.VALUE

AND v.parameter = 'NLS_LANGUAGE';

RAISE_APPLICATION_ERROR (code_in, l_message);

END;

When you make use of RAISE_APPLICATION_ERROR, it is entirely up to you to manage the error numbers and messages. This can get tricky and messy ("Gee, which number should I use? Well, I doubt that anyone would be using -20774!"). To help manage your error codes and provide a consistent interface with which developers can handle server errors, consider building a table to store all the -20,NNN error numbers you use, along with their associated exception names and error messages. Developers can then view these already-defined errors via a screen and choose the one that fits their situation. See the msginfo.pkg file available on the O'Reilly site for one such example of a table, along with code that will generate a package containing declarations of each of the "registered" exceptions.