Lab 11.2 EXCEPTION_INIT Pragma

Lab Objective

After this Lab, you will be able to:

Use EXCEPTION_INIT Pragma

Often your programs need to handle an Oracle error having 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 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 a name of a user -defined error. Once 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:

 DECLARE   exception_name EXCEPTION;   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. The exception_name is the name of your exception, and the error_code is the number of the Oracle error you want to associate with your exception. Consider the following:

FOR EXAMPLE

 DECLARE    v_zip ZIPCODE.ZIP%TYPE := '&sv_zip'; BEGIN    DELETE FROM zipcode     WHERE zip = v_zip;    DBMS_OUTPUT.PUT_LINE ('Zip 'v_zip       ' has been deleted');    COMMIT; END; 

In this example, the record corresponding to the value of zipcode provided by a user is deleted from the ZIPCODE table. Next, the message that a specific zipcode has been deleted is displayed on the screen.

Compare the results running this example entering 06870 for the value of v_zip . The example produces the following output:

  Enter value for sv_zip: 06870   old   2:    v_zip ZIPCODE.ZIP%TYPE := '&sv_zip';   new   2:    v_zip ZIPCODE.ZIP%TYPE := '06870';   DECLARE   *   ERROR at line 1:   ORA-02292: integrity constraint (STUDENT.STU_ZIP_FK)   violated - child record found   ORA-06512: at line 4  

The error message generated by this example occurs because you are trying to delete a record from the ZIPCODE table while its child records exist in the STUDENT table, thus violating the referential integrity constraint STU_ZIP_FK. In other words, there is a record with a foreign key (STU_ZIP_FK) in the STUDENT table (child table) that references a record in the ZIPCODE table (parent table).

Notice that this error has Oracle error number ORA-02292 assigned to it, but it does not have a name. As a result, you need to associate this error number with a user-defined exception, so you can handle this error in the script.

Contrast the example if you modify it as follows (all changes are shown in bold letters ):

FOR EXAMPLE

 DECLARE    v_zip ZIPCODE.ZIP%TYPE := '&sv_zip';  e_child_exists EXCEPTION;   PRAGMA EXCEPTION_INIT(e_child_exists, -2292);  BEGIN    DELETE FROM zipcode     WHERE zip = v_zip;    DBMS_OUTPUT.PUT_LINE ('Zip 'v_zip       ' has been deleted');    COMMIT;  EXCEPTION   WHEN e_child_exists THEN   DBMS_OUTPUT.PUT_LINE ('Delete students for this '   ' zipcode first');  END; 

In this example, you declare the exception e_child_exists . Then you associate the exception with the error number -2292. It is important to note you do not use ORA-02292 in the EXCEPTION_INIT pragma. Next, you add the exception-handling section to the PL/SQL block, so you trap this error. Notice that even though the exception e_child_exists is user-defined, you do not use the RAISE statement, as you saw in Chapter 10. Why do you think you don't use the RAISE statement?

When you run this example using the same value of zipcode, the following output is produced:

  Enter value for sv_zip: 06870   old   2:    v_zip ZIPCODE.ZIP%TYPE := '&sv_zip';   new   2:    v_zip ZIPCODE.ZIP%TYPE := '06870';   Delete students for this zipcode first   PL/SQL procedure successfully completed.  

Notice that this output contains a new error message displayed by the DBMS_OUTPUT.PUT_LINE statement. This version of the output is more descriptive than the previous version. Remember that the user of the program probably does not know about the referential integrity constraints existing in the database. Therefore, the EXCEPTION_INIT pragma improves the readability of your error-handling interface. If the need arises, you can use multiple EXCEPTION_INIT pragmas in your program.