An exception is an error thrown:
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:
Some common internal exceptions have predefined names, such as:
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.
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;
User-defined exceptions must be raised explicitly by:
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:
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.
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;
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:
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).
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) ;