Exception handling in PL/SQL

During runtime, the abnormal program flow which occurs within a precompiled program unit with the actual data is known as an exception. Such errors can be trapped in the EXCEPTION section of a PL/SQL block. The exception handlers within the section can capture the appropriate error and redirect the program flow for an alternative or final task. An efficient exception handling ensures safe and secure termination of the program. The situation without exceptions may become serious if the program involves transactions and the program doesn't handle the appropriate exception, thus ending up in abrupt termination of the program.

There are two types of exceptions—system-defined exceptions and user defined exceptions. While system defined exceptions are implicitly raised by the Oracle server, user-defined exceptions follow different ways to be explicitly raised within the program.

In addition, Oracle avails two utility functions, SQLCODE and SQLERRM, to retrieve the error code and message for the last occurred exception.

System-defined exceptions

As the name suggests, the system-defined exceptions are defined and maintained implicitly by the Oracle server. They are defined in the Oracle STANDARD package. Whenever an exception occurs inside the program. The Oracle server matches and identifies the appropriate exception from the available set of exceptions. Majorly, these exceptions have a negative error code associated with it. In addition to the error code and error message, the system-defined exceptions have a short name which is used with the exception handlers.

For example, ORA-01422 is the error code for the TOO_MANY_ROWS exception whose error message is "exact fetch returns more than requested number of rows". But the name is required only in exception handlers.

The PL/SQL block contains a SELECT statement which selects the name and salary of an employee whose employee ID is one of the declared variables. Note that such SELECT statements are more prone to the NO_DATA_FOUND exception.

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUT ON

/*Start the PL/SQL block*/
SQL> DECLARE
       /*Declare the local variables*/
       L_ENAME VARCHAR2 (100);
       L_SAL NUMBER;
       L_EMPID NUMBER := 8376;
      BEGIN
       /*SELECT statement to fetch the name and salary details of the employee*/
       SELECT ENAME, SAL
       INTO L_ENAME, L_SAL
       FROM EMPLOYEES
       WHERE EMPNO = L_EMPID;
      EXCEPTION
       /*Exception Handler when no data is fetched from the table*/
       WHEN NO_DATA_FOUND THEN
       /*Display an informative message*/
         DBMS_OUTPUT.PUT_LINE ('No Employee exists with the id '||L_EMPID);
      END;
      /

No Employee exists with the id 837

PL/SQL procedure successfully completed.

The following table consolidates some of the common system-defined exceptions along with their ORA error code:

Error	Named exception	Comments (raised when)
ORA-00001	DUP_VAL_ON_INDEX	Duplicate value exists
ORA-01001	INVALID_CURSOR	Cursor is invalid
ORA-01012	NOT_LOGGED_ON	User is not logged in
ORA-01017	LOGIN_DENIED	System error occurred
ORA-01403	NO_DATA_FOUND	The query returns no data
ORA-01422	TOO_MANY_ROWS	A single row query returns multiple rows
ORA-01476	ZERO_DIVIDE	A number is attempted to divide by zero
ORA-01722	INVALID_NUMBER	The number is invalid
ORA-06504	ROWTYPE_MISMATCH	Mismatch occurred in row type
ORA-06511	CURSOR_ALREADY_OPEN	Cursor is already open
ORA-06531	COLLECTION_IS_NULL	Working with NULL collection
ORA-06532	SUBSCRIPT_OUTSIDE_LIMIT	Collection index out of range
ORA-06533	SUBSCRIPT_BEYOND_COUNT	Collection index out of count

User-defined exceptions

Sometimes, the programs are expected to follow agile convention norms of an application. The programs must have standardized error codes and messages. Oracle gives flexibility in declaring and implementing your own exceptions through user-defined exceptions.

Unlike system-defined exceptions, they are raised explicitly in the BEGIN…END section using the RAISE statement.

There are three ways of declaring user-defined exceptions:

• Declare the EXCEPTION type variable in the declaration section. Raise it explicitly in the program body using the RAISE statement. Handle it in the EXCEPTION section. Note that here no error code is involved.

• Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.

  Note

  A Pragma is a clue to the compiler to manipulate the behavior of the program unit during compilation, and not at the time of execution.

  PRAGMA EXCEPTION_INIT can also be used to map an exception to a non-predefined exception. These are standard errors from the Oracle server, but not defined as PL/SQL exceptions.

• Use the RAISE_APPLICATION_ERROR to declare own error number and error message.

The following PL/SQL block declares a user-defined exception and raises it in the program body:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a bind variable M_DIVISOR*/
SQL> VARIABLE M_DIVISOR NUMBER;

/*Declare a bind variable M_DIVIDEND*/
SQL> VARIABLE M_DIVIDEND NUMBER;

/*Assign value to M_DIVISOR as zero*/
SQL> EXEC :M_DIVISOR := 0;

PL/SQL procedure successfully completed.

/*Assign value to M_DIVIDEND as 10/
SQL> EXEC :M_DIVIDEND := 10;

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> DECLARE
        /*Declare the local variables and initialize with the bind variables*/
        L_DIVISOR NUMBER := :M_DIVISOR;
        L_DIVIDEND NUMBER := :M_DIVIDEND;
        L_QUOT NUMBER;
        /*Declare an exception variable*/
        NOCASE EXCEPTION;
     BEGIN
        /*Raise the exception if Divisor is equal to zero*/
        IF L_DIVISOR = 0 THEN
           RAISE NOCASE;
        END IF;
        L_QUOT := L_DIVIDEND/L_DIVISOR;
        DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
     EXCEPTION
       /*Exception handler for NOCASE exception*/
        WHEN NOCASE THEN
           DBMS_OUTPUT.PUT_LINE('Divisor cannot be equal to zero');
     END;
     /
Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

/*Assign a non zero value to M_DIVISOR and execute the PL/SQL block again*/
SQL> EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

SQL> /
The result : 5

PL/SQL procedure successfully completed.

The RAISE_APPLICATION_ERROR procedure

Oracle gives privilege to the database programmers to create their own error number and associate an error message, too. These are dynamic user defined exceptions and are done through an Oracle-supplied method, RAISE_APPLICATION_ERROR. It can be implemented either in the executable section to capture specific and logical errors, or it can be used in the exception section to handle errors of a generic nature.

The syntax for the RAISE_APPLICATION_ERROR procedure is as follows:

RAISE_APPLICATION_ERROR (error_number, error_message[, {TRUE | FALSE}])

In this syntax, the error_number parameter is a mandatory formal parameter whose value must be in the range of -20000 to -20999. The second parameter, error_message, corresponds to the error number and appears with the exception when raised in the program. The last parameter is the optional parameter which allows the error to be added to the current error stack. By default, its value is FALSE.

The following program rewrites the last program by creating a user-defined exception, dynamically (note that it doesn't have the EXCEPTION type variable):

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a bind variable M_DIVISOR*/
SQL> VARIABLE M_DIVISOR NUMBER;

/*Declare a bind variable M_DIVIDEND*/
SQL> VARIABLE M_DIVIDEND NUMBER;

/*Assign value to M_DIVISOR as zero*/
SQL> EXEC :M_DIVISOR := 0;

PL/SQL procedure successfully completed.

/*Assign value to M_DIVIDEND as 10/
SQL> EXEC :M_DIVIDEND := 10;

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> DECLARE
          /*Declare the local variables and initialize them with bind variables*/
          L_DIVISOR NUMBER := :M_DIVISOR;
          L_DIVIDEND NUMBER := :M_DIVIDEND;
          L_QUOT NUMBER;
     BEGIN
         /*Raise the exception using RAISE_APPLICATION_ERROR is the divisor is zero*/
          IF L_DIVISOR = 0 THEN
             RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero');
          END IF;
          L_QUOT := L_DIVIDEND/L_DIVISOR;
          DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
     EXCEPTION
         /*Print appropriate message in OTHERS exception handler*/
        WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
     /

ORA-20005: Divisor cannot be equal to zero

PL/SQL procedure successfully completed.

/*Assign a non zero value to M_DIVISOR and check the output of the PL/SQL block*/
SQL> EXEC :M_DIVISOR := 2;

PL/SQL procedure successfully completed.

SQL> /
The result : 5

PL/SQL procedure successfully completed.

As soon as the exception is raised through RAISE_APPLICATION_ERROR, the program control skips the further execution and jumps to the EXCEPTION section. As there is no exception name mapped against this error code, only OTHERS exception handler can handle the exception.

If a EXCEPTION variable has been declared and mapped to the same user-defined error number, the exception handler can be created with the exception variable. Let us rewrite the preceding program to include an exception variable and suitable exception handler. The following program demonstrates the working of user-defined exceptions and dynamic user-defined exceptions in a single program:

/*Enable the SERVEROUTPUT parameter to print the results in the environment*/
SQL> SET SERVEROUTPUT ON

/*Declare a bind variable M_DIVISOR*/
SQL> VARIABLE M_DIVISOR NUMBER;

/*Declare a bind variable M_DIVIDEND*/
SQL> VARIABLE M_DIVIDEND NUMBER;

/*Assign value to M_DIVISOR as zero*/
SQL> EXEC :M_DIVISOR := 0;

PL/SQL procedure successfully completed.

/*Assign value to M_DIVIDEND as 10/
SQL> EXEC :M_DIVIDEND := 10;

PL/SQL procedure successfully completed.

/*Start the PL/SQL block*/
SQL> DECLARE
          /*Declare an exception variable*/
          NOCASE EXCEPTION;
          /*Declare the local variables and initialize them with bind variables*/
          L_DIVISOR NUMBER := :M_DIVISOR;
          L_DIVIDEND NUMBER := :M_DIVIDEND;
          L_QUOT NUMBER;
          /*Map the exception with a non predefined error number*/
          PRAGMA EXCEPTION_INIT(NOCASE,-20005);
     BEGIN
          /*Raise the exception using RAISE statement if the divisor is zero*/
          IF L_DIVISOR = 0 THEN
             RAISE_APPLICATION_ERROR(-20005,'Divisor cannot be equal to zero');
          END IF;
          L_QUOT := L_DIVIDEND/L_DIVISOR;
          DBMS_OUTPUT.PUT_LINE('The result : '||L_QUOT);
     EXCEPTION
         /*Include exception handler for NOCASE exception*/
        WHEN NOCASE THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
     END;
     /
ORA-20005: Divisor cannot be equal to zero

PL/SQL procedure successfully completed. 

Exception propagation

Exception propagation is an important concept when dealing with nested blocks. A propagating exception always searches for the appropriate exception handler until its last host. The search starts from the EXCEPTION section of the block, that raised it, and continues abruptly until the host environment is reached. As soon as the exception handler is found, the program control resumes the normal flow.

The following cases demonstrate the propagation of exception which is raised in the innermost block:

• Case 1: The following diagram shows the state of a nested PL/SQL block. The inner block raises an exception which is handled in its own EXCEPTION section:

  

  Exception A is raised by the inner block. The inner block handles the exception A within its scope. After the exception is handled, the program control resumes the flow with statements after the inner block in the outer block.

• Case 2: The following diagram shows the state of a nested PL/SQL block where the inner block raises an exception but does not handle the same in its own EXCEPTION section. The EXCEPTION section of the outer block handles the raised exception:

  

  The inner block raises the exception A but does not handle it, so it gets propagated to the EXCEPTION section of the enclosing outer block. Note the abrupt skipping of statements in the outer block.

  Now, the outer block handles the exception A. The exception propagated from the inner block is handled in the outer block and is then terminated.

• Case 3: The following diagram shows the state of a nested PL/SQL block where both the inner and outer block doesn't handle the exception raised in the inner block:

  

  Handling for exception A is missing in the inner as well as the outer block. As a result, the unhandled exception error is raised. The exception is propagated to the host with an error message and the program is terminated abruptly.