PL/SQL Exception Handling

What is PL/SQL Exception or Errors?

In PL/SQL, an error condition is called an exception. An exception can be either internally defined (by the run-time system) or user-defined. Examples of internally defined exceptions are ORA-22056 (value string is divided by zero) and ORA-27102 (out of memory).

You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. User-defined exceptions must be given names.

What is PL/SQL Exception Handling?

When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram.

Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.

To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment.

Example of Exception occurring:

SET SERVEROUTPUT ON ;
DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 0;
   ratio NUMBER;
BEGIN

   ratio := num1 / num2;
   DBMS_OUTPUT.PUT_LINE('Ratio = ' || RATIO);

END;
/

Output:
Error report:
ORA-01476: divisor is equal to zero
ORA-06512: at line 7
01476. 00000 -  "divisor is equal to zero"

Example of Exception Handling:

SET SERVEROUTPUT ON ;
DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 0;
   ratio NUMBER;
BEGIN
   ratio := num1 / num2;
   DBMS_OUTPUT.PUT_LINE('Ratio = ' || RATIO);

EXCEPTION  -- exception handlers begin
-- Only one of the WHEN blocks is executed.
   WHEN ZERO_DIVIDE THEN  -- handles 'division by zero' error
      DBMS_OUTPUT.PUT_LINE('Divide by Zero Error.');
      ratio := NULL;
   WHEN OTHERS THEN  -- handles all other errors
      DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
      RATIO := NULL;
END;   -- exception handlers and block end here
/

Output:
anonymous block completed
Divide by Zero Error.

Defining Your Own PL/SQL Exceptions:

A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. The latter lets you associate an error message with the user-defined exception.

Declaring & Invoking PL/SQL Exceptions:

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. You declare an exception by introducing its name, followed by the keyword EXCEPTION.

SET SERVEROUTPUT ON ;
DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 20;
   myException EXCEPTION;  -- Decalare Exception
BEGIN
   IF num1 < num2 THEN
     RAISE myException;  -- Raise Exception 
   END IF;
EXCEPTION
   WHEN myException THEN  -- Handle Raised Exception
      DBMS_OUTPUT.PUT_LINE('num1 is less than num2');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
END;
/

Output:
anonymous block completed
num1 is less than num2

Scope Rules for PL/SQL Exceptions

You cannot declare an exception twice in the same 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 re declare 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.

Associating a PL/SQL Exception with a Number (EXCEPTION_INIT Pragma):

To handle error conditions (typically ORA-n messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive that is processed at compile time, not at run time.

In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle Database error number. That lets you refer to any internal exception by name and to write a specific handler for it.

SET SERVEROUTPUT ON ;
DECLARE
   deadlock_detected EXCEPTION;
   PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
   NULL; -- Some operation that causes an ORA-00060 error
EXCEPTION
   WHEN deadlock_detected THEN
      DBMS_OUTPUT.PUT_LINE('deadlock_detected error occurred.');
END;
/

Defining Your Own Error Messages (RAISE_APPLICATION_ERROR Procedure):

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.

To invoke RAISE_APPLICATION_ERROR, use the following syntax:

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

where error_number is a negative integer in the range -20000..-20999 and 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.

SET SERVEROUTPUT ON ;
DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 20;
BEGIN
 IF NUM1 < NUM2 THEN
   RAISE_APPLICATION_ERROR(-20001, 'num1 is less than num2', TRUE);
 END IF;
END;
/

Output:
Error report:
ORA-20001: num1 is less than num

Retrieving the Error Code and Error Message:

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 packaged function DBMS_UTILTY.FORMAT_ERROR_STACK or the built-in function SQLERRM.

SQLERRM returns a maximum of 512 bytes, which is the maximum length of an Oracle Database error message. 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

Example for displaying SQLCODE and SQLERRM:

SET SERVEROUTPUT ON ;
DECLARE
   num1 NUMBER := 10;
   num2 NUMBER := 0;
   ratio NUMBER;
   v_code  NUMBER;
   v_errm  VARCHAR2(64);
BEGIN
   ratio := num1 / num2;
   DBMS_OUTPUT.PUT_LINE('Ratio = ' || RATIO);

EXCEPTION
   WHEN ZERO_DIVIDE THEN
     v_code := SQLCODE;
     v_errm := SUBSTR(SQLERRM, 1, 64);
     DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
END;
/

Output:
anonymous block completed
Error code -1476: ORA-01476: divisor is equal to zero

Next Article: PL/SQL Commands

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)