8.7.1 Using RAISE_APPLICATION_ERROR

Oracle provides the RAISE_APPLICATION_ERROR procedure to facilitate client-server error communication. The header for this procedure is shown below:

PROCEDURE RAISE_APPLICATION_ERROR
   (error_number_in IN NUMBER, error_msg_in IN VARCHAR2)

Here is an example of a call to this built-in:

RAISE_APPLICATION_ERROR (-20001, 'You cannot hire babies!');

When you call RAISE_APPLICATION_ERROR, it is as though an exception has been raised with the RAISE statement. 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 built-in returns a programmer-defined error number and message back to the client component of the application. You can then use the EXCEPTION_INIT pragma and exception handlers to handle the error in a graceful, user-friendly fashion.

The error number you specify must be between -20000 and -20999 so you do not conflict with any Oracle error numbers.

The error message can be up to 2K bytes in length; if it is longer, it will not abort the call to RAISE_APPLICATION_ERROR; the procedure will simply truncate anything beyond the 2K.

The exception handler architecture, combined with RAISE_APPLICATION_ERROR and the On-Error trigger, allows your front-end application to rely on business rules embedded in the database to perform validation and communicate problems to the user. When you make use of RAISE_APPLICATION_ERROR, however, it is entirely up to you to manage the error numbers and messages. This can get tricky and messy. To help manage your error codes and provide a consistent interface with which developers can handle server errors, you might consider building a package.

8.7.2 RAISE_APPLICATION_ERROR in a database trigger

Suppose you need to implement a database trigger which stops records from being inserted into the database if the person is less than 18 years old. The code for this trigger would look like this:

CREATE OR REPLACE TRIGGER minimum_age_check
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
   IF ADD_MONTHS (:new.birth_date, 18*12) > SYSDATE
   THEN
      RAISE_APPLICATION_ERROR
         (-20001, 'Employees must at least eighteen years of age.');
   END IF;
END;

On the client side, I can write a program like the following to detect and handle this exception:

DECLARE
   /* Declare the exception. */
   no_babies_allowed EXCEPTION;

   /* Associate the name with the error number used in the trigger. */
   PRAGMA EXCEPTION_INIT (no_babies_allowed, -20001);

BEGIN
   /* Attempt to insert the employee. */
   INSERT INTO employee ... ;

EXCEPTION

   /* Handle the server-side exception. */
   WHEN no_babies_allowed
   THEN
      /*
      || SQLERRM will return the message passed into the
      || RAISE_APPLICATION_ERROR built-in.
      */
      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;