RSS

8.5 EXCEPTION_INIT Pragma

04 May

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. Consider the following:

 

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;

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

 

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;

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.

Advertisements
 
Leave a comment

Posted by on 05/04/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: