Oracle PL SQL Exception Handling

Exception Types:

• Internal or System Exceptions
• User Defined Exceptions

Declaring & Raising Exceptions:

DECLARE
   invalid_format EXCEPTION;
BEGIN
   ......................
   ..................
   IF.....THEN
      RAISE invalid_format;
   END IF;
   .....................
EXCEPTION
WHEN invalid_format THEN
   ...............
END;

Pragma EXCEPTION_INIT:
This is for assigning exception names to Oracle error/exception codes that do not have predefined names. Normally we see these kind of exceptions being handled using OTHERS block, but assigning names using Pragma is more cleaner approach. Pragma is evaluated during compile time.

This assignment of name to error code is done in the declaration section:

DECLARE
   PRAGMA EXCEPTION_INIT(network_error, -12541);
BEGIN
   ......................................
EXCEPTION
WHEN network_error THEN
   .............
END;

RAISE_APPLICATION_ERROR:
This lets you raise user defined ORA- exceptions. This helps in handling business logic exceptions in a more cleaner way than returning unhandled exceptions to the caller.

RAISE_APPLICATION_ERROR(
<error number from -20000 to -20999>,
<error message>,
<TRUE/FALSE - TRUE to keep the maintain the entire error stack, default is FALSE - this will only have the latest error>]);

DECLARE
BEGIN
   IF....THEN
      RAISE_APPLICATION_ERROR(-20001, "Logical Error");
   END IF;
EXCEPTION
END;

To capture the name of the unit/procedure/function from where the error is being raised - you can use $$PLSQL_UNIT (from 10g onwards).  And to capture the line where you are logging the error you can use $$PLQSL_LINE (also from 10g onwards).

Some other useful Oracle constructs that can be used to handle and capture the exceptions effectively:

DBMS_UTILITY.FORMAT_ERROR_STACK
   This returns the formatted error stack

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
   This displays the call stack where the exceptions were raised along with the line numbers

DBMS_UTILITY.FORMAT_CALL_STACK
   This displays the formatted call stack