PL/SQL - (Exception|Error Handling)

An exception is an error thrown:

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

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.

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

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.

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

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

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

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;

User-defined exceptions must be raised explicitly by:

• RAISE statements
• or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

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;

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.

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

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

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;
/

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

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);

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) ;

• Way to get Error Message