PL/SQL - (Exception|Error Handling)
Table of Contents
1 - About
An exception is an error thrown:
- internally (by the run-time system)
- or by a PL/SQL program (user-defined).
2 - Articles Related
3 - 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.
4 - Initialization
4.1 - 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
4.2 - 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.
-- 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;
5 - RAISE
User-defined exceptions must be raised explicitly by:
- RAISE statements
- or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
5..1 - 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;
5..2 - 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.
5..3 - RERAISE
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;
6 - 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;
/
7 - Retrieving the Error Code and Error Message
7.1 - Error Code
In an exception handler, you can retrieve the error code with the built-in function SQLCODE.
7.2 - 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);
7.3 - 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) ;