Oracle in a Nutshell

Exceptions

An exception is returned when an error occurs in your PL/SQL code. When an exception occurs (or is “raised”), control leaves the execution section and passes directly to the exception section of your PL/SQL block.

Some exceptions have been predefined by Oracle in the STANDARD package, or you can declare your own exception with the datatype EXCEPTION, as in:

DECLARE
   exception_name EXCEPTION;

An exception can be declared only once in a block, but nested blocks can declare an exception with the same name as an outer block. If this multiple declaration occurs, scope takes precedence over name when handling the exception. The inner block’s declaration takes precedence over a global declaration.

All declared exceptions have an error code of 1 and the error message “User-defined exception,” unless you use the EXCEPTION_INIT pragma. (See Section 9.6 for a discussion of pragmas in PL/SQL.)

You can associate an error number with a declared exception with the PRAGMA EXCEPTION_INIT statement:

DECLARE
   exception_name EXCEPTION;
   PRAGMA EXCEPTION_INIT (exception_name, error_number);

where error_number is a literal value (variable references are not allowed). This number can be an Oracle error, such as -1855, or an error in the user-definable range of -20000 to -20999.