Using EXCEPTION_INIT

With the EXCEPTION_INIT pragma, I can replace the WHEN clause shown in the previous example with something like this:

EXCEPTION WHEN invalid_month THEN

No more hardcoded literal error numbers that are difficult to remember. Now I have a self-documenting name. Let's see how we can accomplish this.

EXCEPTION_INIT is a compile-time command or pragma used to associate a name with an internal error code. EXCEPTION_INIT instructs the compiler to associate an identifier, declared as an EXCEPTION, with a specific error number. Once you have made that association, you can then raise that exception by name and write an explicit WHEN handler that traps the error.

The pragma EXCEPTION_INIT must appear in the declaration section of a block; the exception named must have already been defined in that same block, an enclosing block, or a package specification. Here is the syntax in an anonymous block:

DECLARE exception_name EXCEPTION; PRAGMA EXCEPTION_INIT (exception_name, integer);

where exception_name is the name of an exception and integer is a literal integer value, the number of the Oracle error with which you want to associate the named exception. The error number can be any integer value with these constraints:

· It cannot be -1403 (the error code for NO_DATA_FOUND). If for some reason you want to associate your own named exception with this error, you need to pass 100 to the EXCEPTION_INIT pragma. (NO_DATA_FOUND is the only error I know of in Oracle that has two different error numbers, a consequence of ANSI standards compliance requiring the use of the error 100 for this condition.)

· It cannot be 0 or any positive number besides 1.

· It cannot be a negative number less than -10000000.

Let's look at an example. In the following program code, I declare and associate an exception for this error:

ORA-2292 violated integrity constraining (OWNER.CONSTRAINT) - child record found.

This error occurs if I 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;