PL/SQL - (Exception|Error Handling)

About

An exception is an error thrown:

internally (by the run-time system)
or by a PL/SQL program (user-defined).

Articles Related

Rules

You cannot declare an exception twice in the same BEGIN/END block. You can, however, declare the same exception in two different blocks.

Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosing blocks cannot reference exceptions declared in a sub-block.

If you redeclare a global exception in a sub-block, the local declaration prevails. The sub-block cannot reference the global exception, unless the exception is declared in a labeled block and you qualify its name with the block label block_label.exception_name.

Initialization

Internally defined

Examples of internally defined exceptions are:

ORA-22056 (value string is divided by zero)
and ORA-27102 (out of memory).

Some common internal exceptions have predefined names, such as:

ZERO_DIVIDE
STORAGE_ERROR
NO_DATA_FOUND
TOO_MANY_ROWS - ORA-01422
DUP_VAL_ON_INDEX - Duplicate on PK, UK, Index

The other internal exceptions can be given names.

See Predefined PL/SQL Exceptions for a list

User-defined

DECLARE
   -- With the keyword EXCEPTION declare an exception named TABLE_DOES_NOT_EXIST 
   MY_EXCEPTION EXCEPTION;
   -- Associating a PL/SQL Exception with a Number
   PRAGMA EXCEPTION_INIT(MY_EXCEPTION, -942);
BEGIN
   -- Some operation that causes an error
EXCEPTION
   WHEN MY_EXCEPTION THEN
     -- handle the error;
End;

The pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle Database error number (ORA-n error).

PRAGMA EXCEPTION_INIT(exception_name, Negative Oracle_error_number);

That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

You normally defined them in a package specification. It permits to the developer to use them again.

-- Exception code (Null)
c_error_null_parameter      CONSTANT NUMBER := - 20000;

e_error_null_parameter      EXCEPTION;
pragma exception_init( e_error_null_parameter, - 20000 );

Then you send it from your code with:

IF (p_para is null) THEN
       raise_application_error(c_error_null_parameter, 'The parameter can not be null', TRUE);
END IF;

And it can be received:

BEGIN
    my_procedure(p_para VARCHAR2);
EXCEPTION
   WHEN e_error_null_parameter THEN
       DBMS_OUTPUT.println('Do something, a parameter is null' || SQLERRM || DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

RAISE

User-defined exceptions must be raised explicitly by:

RAISE statements
or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

RAISE

DECLARE
   -- With the keyword EXCEPTION declare an exception named TABLE_DOES_NOT_EXIST 
   MY_EXCEPTION EXCEPTION;
   -- Associating a PL/SQL Exception with a Number
   PRAGMA EXCEPTION_INIT(MY_EXCEPTION, -942);
BEGIN
   -- Some operation that causes an error
   RAISE MY_EXCEPTION;
EXCEPTION
   WHEN MY_EXCEPTION THEN
     -- handle the error;
End;

RAISE_APPLICATION_ERROR

The RAISE_APPLICATION_ERROR procedure lets you issue user-defined ORA-n error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.

raise_application_error(error_number, message[, {TRUE | FALSE (Default)}]);

where:

error_number is a negative integer in the range -20000..-20999
message is a character string up to 2048 bytes long.
If the optional third parameter is TRUE, the error is placed on the stack of previous errors. If the parameter is FALSE (the default), the error replaces all previous errors.

RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you need not qualify references to it.

RERAISE

To reraise an exception, use a RAISE statement without an exception name, which is allowed only in an exception handler.

This action will delete the whole stack trace. You need to get it before reraising with DBMS_UTILITY.FORMAT_ERROR_STACK before.

BEGIN  ---------- sub-block begins
   RAISE salary_too_high;  -- raise the exception
EXCEPTION
    WHEN salary_too_high THEN
      RAISE;  -- reraise the current exception
END;

Handler

You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL program.

BEGIN

   -- An action that raise an error

EXCEPTION  -- exception handlers begin
   
   WHEN ZERO_DIVIDE OR STANDARD.INVALID_NUMBER THEN  
      --- Do Something
   WHEN OTHERS THEN  -- handles all other errors
      --- Do Something

END;
/

Retrieving the Error Code and Error Message

Error Code

In an exception handler, you can retrieve the error code with the built-in function SQLCODE.

Error Message

To retrieve the associated error message, you can use either:

the built-in function SQLERRM.

SQLERRM returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message (including the error code, nested messages, and message inserts, such as table and column names).

or the packaged function DBMS_UTILTY.FORMAT_ERROR_STACK

DBMS_UTILTY.FORMAT_ERROR_STACK returns the full error stack, up to 2000 bytes. Therefore, DBMS_UTILTY.FORMAT_ERROR_STACK is recommended over SQLERRM, except when using the FORALL statement with its SAVE EXCEPTIONS clause. With SAVE EXCEPTIONS, use SQLERRM,

EXCEPTION
     WHEN OTHERS THEN
        v_code := SQLCODE;
        v_errm := SUBSTR(SQLERRM, 1, 64);

Error stack

The function DBMS_UTILITY.FORMAT_ERROR_STACK formats the current error stack.

dbms_output.put_line( 'Error - Stack Trace: ' || SQLERRM || ' -  ' || dbms_utility.format_error_backtrace) ;

Documentation / Reference

Way to get Error Message