Exceptions (User-Defined/Oracle Defined)?

Oracle includes about 20 predefined exceptions (errors) - we can allow Oracle to raise these implicitly. For errors that don't fall into the predefined categories - declare in advance and allow oracle to raise an exception. For problems that are not recognized as an error by Oracle - but still cause some difficulty within your application - declare a User Defined Error and raise it explicitly

Trap non-predefined errors by declaring them. You can also associate the error no. with a name so that you can write a specific handler. This is done with the PRAGMA EXCEPION_INIT pragma. PRAGMA (pseudoinstructions) indicates that an item is a 'compiler directive' running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error. When an exception occurs you can identify the

associated error code/message with two supplied functions SQLCODE and SQLERRM.

Trapping user-defined exceptions

DECLARE the exception

RAISE the exception

Handle the raised exception

Propagation of Exception handling in sub blocks

If a sub block does not have a handler for a particular error it will propagate to the

enclosing block - where it can be caught by more general exception handlers.

RAISE_APPLICATION_ERROR (error_no, message [, {TRUE|FALSE}]);

This procedure allows user defined error messages from stored sub programs - call only from stored sub prog.

Error_no = a user defined no (between -20000 and -20999)

TRUE = stack errors

FALSE = keep just last

This can either be used in the executable section of code or the exception section