After completing this lab, you will be able to
- Use the EXCEPTION_INIT pragma
Often your programs need to handle an Oracle error that has a particular number associated with it, but no name by which it can be referenced. As a result, you are unable to write a handler to trap this error. In a case like this, you can use a construct called a pragma. A pragma is a special instruction to the PL/SQL compiler. It is important to note that pragmas are processed at the time of the compilation. The EXCEPTION_INIT pragma allows you to associate an Oracle error number with the name of a user-defined error. After you associate an error name with an Oracle error number, you can reference the error and write a handler for it.
The EXCEPTION_INIT pragma appears in the declaration section of a block as shown:
PRAGMA EXCEPTION_INIT(exception_name, error_code);
Notice that the declaration of the user-defined exception appears before the EXCEPTION_INIT pragma where it is used. The EXCEPTION_INIT pragma has two parameters: exception_name and error_code. exception_name is the name of your exception, and error_code is the number of the Oracle error you want to associate with your exception.
Oracle error number ORA-02292 is assigned to cascade delete error, but it does not have a name. As a result, you need to associate this error number with a user-defined exception so that you can handle this error in the script:
v_zip zipcode.zip%type := ‘&sv_zip’;
PRAGMA EXCEPTION_INIT(e_child_exists, –2292);
DELETE FROM zipcode
WHERE zip = v_zip;
DBMS_OUTPUT.PUT_LINE (‘Zip ‘||v_zip||‘ has been deleted’);
WHEN e_child_exists THEN
DBMS_OUTPUT.PUT_LINE (‘Delete students for this ‘|| ‘zipcode first’);