A script-enabled browser is required for this page to function properly.

User-Defined Exceptions

Although PL/SQL includes many pre-defined exceptions—such as NO_DATA_FOUND, DUP_VAL_ON_INDEX, and VALUE_ERROR— they will never completely cover the range of ORACLE errors you may need to trap. So PL/SQL provides the facility to define your own exceptions and associate them with the occurrence of a particular Oracle error of your choice. The following code illustrates how to use EXCEPTION_INIT to tell PL/SQL to report an error of your choice. For more information refer to the PL/SQL User's Guide and Reference.

User-defined exceptions Examples

Example

/*
** Example of declaring your own error-driven exceptions
*/

DECLARE
/* 

** First declare the name of the exception 

*/ 

cannot_del_parent EXCEPTION;  

/* 

** Then associate it with the ORA-2292 error 

** which is "violated integrity constraint XYZ - 
** child record found". Note error number is negative. 
*/ 

PRAGMA Exception_Init (cannot_del_parent, -2292);

BEGIN
DELETE FROM PARENT_TABLE 
WHERE PRIMARY_KEY_FIELD = :BLOCK.PK; 

/* 

** If we get here, then things went ok. 

*/ 

EXCEPTION

/* 

** If our error arises, then this exception 
** will be raised. We can deal with it elegantly. 
*/ 

WHEN cannot_del_parent THEN 
Message('You cannot remove open '||  
'order number'||:block.pk);  
RAISE Form_Trigger_Failure;  

END;

This method is best when the ORACLE error number itself is enough to allow your application to determine what happened. User-defined error messages can be returned from database triggers, procedures, or functions, as shown earlier with RAISE_APPLICATION_ERROR. Creating corresponding user-defined exceptions is a natural counterpart to trapping the errors you raise.

However, some errors returned by the kernel contain the name of the constraint (out of many possible ones) that has been violated, always returning a single ORACLE error number. An example of this would be:

ORA-02290: violated check constraint (SCOTT.C_CK)


About using PL/SQL exception handling in triggers

About trigger failure results

About evaluating the success or failure of Built-ins