1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Exception Handling in PL/SQL

Discussion in 'SQL PL/SQL' started by SBH, Dec 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Introduction

    Exception is an uneven break in the program flow due to logical issues in the PL/SQL block. It is raised by Oracle server and the execution process is aborted as soon as it is encountered. It can be trapped and handled in the EXCEPTION section of a PL/SQL block. Note that it is an optional section in a block, which plays a vital role in large applications.
    [​IMG]

    From the above figure, the execution control moves to EXCEPTION section (if available), to check whether the current exception has been handled or not. If it has been handled, then set of statements are executed before successful termination, else it terminated and the exception raised propagates to the host.

    For example, the below PL/SQL block selects an employee with the name CLUB, which does not exists. Therefore, Oracle server raises an exception NO_DATA_FOUND and terminates the block just after raising the exception.

    Code (SQL):
    DECLARE
     L_EMPID NUMBER;
     L_DEPTNO NUMBER;
    BEGIN
     DBMS_OUTPUT.PUT_LINE ('Selecting an Employee');
     SELECT    EMPID,  DEPTNO
     INTO L_EMPID, L_DEPTNO
     FROM EMPLOYEE
     WHERE ENAME = 'CLUB';
     DBMS_OUTPUT.PUT_LINE ('Employee selected');
    END;
    /

    Selecting an Employee
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no DATA found   
    ORA-06512: at line 6
    2. Trapping Exceptions

    All exceptions raised in the PL/SQL section are trapped in EXCEPTION section of the block. Below is the syntax of catching and handling exceptions.

    Code (Text):
    EXCEPTION
      WHEN exception1 [OR exception2 . . .] THEN
        statement1;
        statement2;
        . . .
      [WHEN OTHERS THEN
        statement1;
        statement2;
        . . .]
    3. Notes

    i. Multiple exception handlers can reside in an EXCEPTION section, where each one must be handled once.
    ii. Only one exception can be executed in a single program execution.
    iii. WHEN OTHERS is a generic exception, which can substitute any unknown exception. It must be handled once and at the end of all handlers.

    4. Handling Exceptions in PL/SQL

    As we saw in first example, Oracle server implicitly raises the exception encountered during block execution, if it is not handled by the programmer. These exceptions can be handled to direct an alternate action in the Exception section. Note that once the execution pointer enters EXCEPTION section, it moves forward direction only to terminate the block.

    Predefined exception can be explicitly raised at any stage in the block, if required, by the developer using RAISE statement.
    Programmer can also prototype his own exception and raise the same using RAISE statement.

    5. Pre – defined exceptions

    Oracle server defines a bracket of exceptions. If unhandled, they are raised implicitly by the server, which we have already noticed in earlier examples. Else they can be raised and handled explicitly too.
    Below table lists some commonly used exceptions. They are predefined in oracle STANDARD package.

    [TABLE]Error Named Exception
    ORA-00001 DUP_VAL_ON_INDEX
    ORA-00051 TIMEOUT_ON_RESOURCE
    ORA-01001 INVALID_CURSOR
    ORA-01012 NOT_LOGGED_ON
    ORA-01017 LOGIN_DENIED
    ORA-01403 NO_DATA_FOUND
    ORA-01410 SYS_INVALID_ROWID
    ORA-01422 TOO_MANY_ROWS
    ORA-01476 ZERO_DIVIDE
    ORA-01722 INVALID_NUMBER
    ORA-06500 STORAGE_ERROR
    ORA-06501 PROGRAM_ERROR
    ORA-06502 VALUE_ERROR
    ORA-06504 ROWTYPE_MISMATCH
    ORA-06511 CURSOR_ALREADY_OPEN
    ORA-06530 ACCESS_INTO_NULL
    ORA-06531 COLLECTION_IS_NULL
    ORA-06532 SUBSCRIPT_OUTSIDE_LIMIT
    ORA-06533 SUBSCRIPT_BEYOND_COUNT[/TABLE]

    6. Raising Pre-defined exceptions explicitly

    System defined exceptions can be explicitly raised and handled by the programmer. The PL/SQL block below fetches the employees with JOB_ID as QTY. If no employee exists with such job id, program must show appropriate message.

    Code (SQL):
    DECLARE
     CURSOR C IS
      SELECT EMPID
      FROM EMPLOYEE
      WHERE JOB_ID = 'QTY';
     
     L_EMPID VARCHAR2(100);

    BEGIN
     OPEN C;
     FETCH C INTO L_EMPID;
     IF C%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
     END IF;
     CLOSE C;

    EXCEPTION
     WHEN NO_DATA_FOUND THEN
     DBMS_OUTPUT.PUT_LINE('NO EMPLOYEE EXIST WITH JOB ID QTY');
    END;
    /

    NO EMPLOYEE EXIST WITH JOB ID QTY

    PL/SQL PROCEDURE successfully completed.

    7. User defined exceptions

    These exceptions are not defined by the oracle server. They are declared in the DECLARE section with type EXCEPTION and used in the execution block based on the requirement. There are two ways of using user defined exceptions in the PL/SQL blocks.

    i. PRAGMA EXCEPTION_INIT to associate exception with an error number
    ii. Just raise the defined exception to pass the control to EXCEPTION section

    Below is the PL/SQL block structure showing usage of User defined exception

    Code (Text):
    DECLARE
    [EXP] EXCEPTION;
    PRAGMA EXCEPTION_INIT([EXP],[ERROR NUMBER], [TRUE | FALSE])
    BEGIN
       [STATEMENTS]
          IF [CONDITION] THEN
             RAISE [EXP]
          END IF;
    ..
    ..
    EXCEPTION
    WHEN [EXP] THEN
    [ACTION]

    END;
    In the above syntax, EXP is the user defined exception name. PRAGMA is a compiler directive which instructs the compiler to interpret occurrences of exception name as that of the error number instead of compilation. Third parameter, FALSE by default, allows to keep error stack contents.

    Example, the PL/SQL block below associates error number -1422 with the user defined exception EX_TEST. Note that -1422 corresponds to exception NO_DATA_FOUND. At any instance of the program execution, if oracle server encounters -1422, it raises EX_TEST exception and jumps to EXCEPTION section.

    Code (SQL):
    DECLARE
     L_EMPID NUMBER;
     EX_TEST EXCEPTION;
     PRAGMA EXCEPTION_INIT(EX_TEST,-1422);
    BEGIN
     SELECT EMPID
     INTO L_EMPID
     FROM EMPLOYEE
     WHERE JOB_ID = 'QTY';
    EXCEPTION
    WHEN EX_TEST THEN
     DBMS_OUTPUT.PUT_LINE('Control Moved to Exception Block');
    END;
    /

    Control Moved TO Exception Block
    PL/SQL PROCEDURE successfully completed.
    Above exception could have also been raised using RAISE statement without using PRAGMA and slight change in code. Execution flow remains same as above. Note the use of cursor in the block.

    Code (SQL):
    DECLARE
     CURSOR C IS
      SELECT EMPID
      FROM EMPLOYEE
      WHERE JOB_ID = 'QTY';
     
     L_EMPID NUMBER;
     EX_TEST EXCEPTION;
    BEGIN
     OPEN C;
     FETCH C INTO L_EMPID;
      IF C%ROWCOUNT = 0 THEN
         RAISE EX_TEST;
      END IF;
     CLOSE C;
    EXCEPTION
    WHEN EX_TEST THEN
     DBMS_OUTPUT.PUT_LINE('Control Moved to Exception Block');
    END;
    /

    Control Moved TO Exception Block
    PL/SQL PROCEDURE successfully completed.
    8. Exception trapping functions
    [​IMG]

    Example, the PL/SQL block below attempts to divide an integer with zero. The exception is handled

    Code (SQL):
    DECLARE
     L_NUM1 NUMBER;
     L_NUM2 NUMBER;

    BEGIN
     L_NUM1 := 10;
     L_NUM2 := 0;
     DBMS_OUTPUT.PUT_LINE('RESULT:'||L_NUM1/L_NUM2);

    EXCEPTION
    WHEN ZERO_DIVIDE THEN
     DBMS_OUTPUT.PUT_LINE(SQLCODE);
     DBMS_OUTPUT.PUT_LINE(SQLERRM);

    END;
    /

    -1476
    ORA-01476: divisor IS equal TO zero

    PL/SQL PROCEDURE successfully completed.
    9. Exception propagation

    Below figures show the propagation of exception.
    [​IMG]

    Figure 1: Inner block raises and handles exception A. After inner block is terminated, control resumes with statements following the inner block in outer block.

    Figure 2: Exception A is raised but not handled in the inner block. It propagates to the Exception section of Outer block, which handles it.

    Figure 3: Inner block raises exception A but not handles it. It propagates to Exception section of Outer block, which has no handling for A. As the result, unhandled exception error is raised i.e. the exception is propagated to the host with an error message and operation aborted.
     

    Attached Files:

    • Exp1.JPG
      Exp1.JPG
      File size:
      16.9 KB
      Views:
      8,187
    • Exp2.JPG
      Exp2.JPG
      File size:
      15.6 KB
      Views:
      2,686
    • Exp3.JPG
      Exp3.JPG
      File size:
      31 KB
      Views:
      2,960