EXCEPTION_INIT pragma

EXCEPTION_INIT pragma:
----------------------------
The EXCEPTION_INIT pragma is used to tell the compiler to associate a particular error number with an exception declared in your program. You should only associate a single error code to a specific exception.

This allows specification of a handler to take care of any exceptions that result from an Oracle error.
If you don't specify the EXCEPTION_INIT clause, you must allow for processing of non-specified exceptions in the OTHERS exception area.

EXCEPTION_INIT Pragma Format
PRAGMA EXCEPTION_INIT(exception_name,error_number)
Click on each parameter in BLUE for an explanation of its use ...

The EXCEPTION_INIT clause must appear in the same declarative section as its associated exception handler.

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (exception_name, error_code_literal);
BEGIN
where exception_name is the name of an exception and error_code_literal is the number of the Oracle error (including the minus sign, if the error code is negative, as is almost always the case).

In the following program code, we declare and associate an exception for this error:

ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) -
         child record found.
This error occurs if we try to delete a parent record while there are child records still in that table.
A child record is a record with a foreign key reference to the parent table:

PROCEDURE delete_company (company_id_in IN NUMBER)
IS
   /* Declare the exception. */
   still_have_employees EXCEPTION;
   /* Associate the exception name with an error number. */
   PRAGMA EXCEPTION_INIT (still_have_employees, -2292);
BEGIN
   /* Try to delete the company. */
   DELETE FROM company
    WHERE company_id = company_id_in;
EXCEPTION
   /* If child records were found, this exception is raised! */
   WHEN still_have_employees
   THEN
      DBMS_OUTPUT.PUT_LINE
         (' Please delete employees for company first.');
END;
When you use EXCEPTION_INIT, you must supply a literal number for the second argument of the pragma call.
By explicitly naming this system exception, the purpose of the exception handler is self-evident.

The EXCEPTION_INIT pragma improves the readability of your programs by assigning names to otherwise obscure error numbers.
You can employ the EXCEPTION_INIT pragma more than once in your program. You can even assign more than one exception name to the same error number.

CLIENT SERVER ERROR COMMUNICATION
--------------------------------------


A conventional approach with procedural constraint enforcement in the client/server arena is to call RAISE_APPLICATION_ERROR from within a trigger. This is done if a procedural constraint is violated, for example, the salaries in the EMP table are all increased to a point where the department sum exceeds some threshold. This error can be caught by the client. In the WebServer environment, this error is written to the log file of the DCD. The end result is the user gets a generic error message on the Web page and they have no clue as to what the problem is.
When developing applications for the WebServer, you must rely more heavily on pragma exception_init. In this case, let’s assume that a stored procedure performs an INSERT that fires a trigger that may raise an application error using the error number -20100. In this case the stored procedure should contain the following:

PROCEDURE process_form(...) IS
   invalid_update EXCEPTION;
   PRAGMA EXCEPTION_INIT(invalid_update, -20100);
BEGIN
   INSERT INTO emp VALUES (...);
EXCEPTION
   WHEN invalid_update THEN
      ..do something so the user gets a new page
      ..with a header message indicating the failure.
 END;

Oracle provides the RAISE_APPLICATION_ERROR procedure to communicate application-specific errors from the server side (usually a database trigger) to the client-side application. This built-in procedure is the only mechanism available for communicating a server-side, programmer-defined exception to the client side in such a way that the client process can handle the exception.
Here is an example of a call to this built-in:

RAISE_APPLICATION_ERROR (-20001, 'Invalid Registration');
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 all the effects of the program are rolled back. 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 manner.

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. To help manage your error codes and provide a consistent interface with which developers can handle server errors, you might consider building a package.

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, a program can be written 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;