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.