With the EXCEPTION_INIT pragma, I can replace the WHEN clause shown in the previous example with something like this:
EXCEPTION WHEN invalid_month THENNo 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;
