RSS

7.7 EXCEPTION_INIT Pragma

22 Apr

Lab Objective

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:

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. 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:

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;

Advertisements
 
Leave a comment

Posted by on 04/22/2010 in ORACLE

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: