PL/SQL Exceptions

Exception Handling in PL/SQL

An exception is an error situation which may arise during program execution

PLSQL supports Oracle named (predefined) and unnamed (not predefined) exceptions as well as user-defined exceptions

In any type of Exception, when the exception takes place the exception is said to be “RAISED”

An Exception Handler is used to specify the response for a raised exception

When an exception is raised, either implicitly or explicitly, the normal execution of the program is abandoned

The control then shifts to the corresponding exception handler if it is present else control shifts to the calling environment

In any case, when an exception is fired then the control can not return to the executable section of the current block

If an exception is handled then the program unit is considered as successful i. e. the effect of the operations performed before the exception is raised, is saved else the earlier operations are rolled back

If an exception is raised in the executable section and there is an associated handler, the exception is trapped (handled)

Handlers for various exceptions can be specified in the EXCEPTION section

A handler for an exception is of the form

      WHEN exception_name THEN

                   a sequence of statements

Example

          EXCEPTION

      WHEN NO_DATA_FOUND THEN

          DBMS_OUTPUT.PUT_LINE('WRONG INPUT');

 END;

   

Types of Exceptions         

Named (predefined) Oracle Exceptions

Unnamed (Not predefined) Oracle Exceptions

User-defined Exceptions

Named Exceptions

Oracle has around 20 named predefined    exceptions

Trapped within the Exception handling block

Some commonly encountered oracle named exceptions:

NO_DATA_FOUND – fired when SELECT INTO statement does not return any row

TOO_MANY_ROWS - fired when SELECT INTO statement returns more than one row

INVALID_CURSOR – fired when trying to FETCH from a cursor that is not open

ZERO_DIVIDE – fired when trying to divide by zero

VALUE_ERROR – fired when trying to put an incompatible value in a variable or column

Raising an Exception

An exception is raised implicitly if any Oracle error occurs e. g. when no rows are retrieved by SELECT statement, PL/SQL raises the exception NO_DATA_FOUND

We raise an exception explicitly by issuing the RAISE statement within the block

Exception Handling: Example

DECLARE

   myname   VARCHAR2 (8);

BEGIN

   SELECT ename

     INTO myname

     FROM emp

    WHERE empno = &eno;

   DBMS_OUTPUT.put_line ('Name of the employee :  ' || myname);

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line ('WRONG EMPLOYEE NUMBER');

   WHEN VALUE_ERROR

   THEN

      DBMS_OUTPUT.put_line (' Employee Name size larger than 8');

END;

/

The OTHERS Handler

Most of the times Exceptions occur because of inappropriate values passed for user inputs

In a good program the probable exceptions are visualized and handled

However for a program, one may not be able to visualize all the probable exceptions

Handling all possible exceptions may make the program very bulky

To overcome this problem a “General Exception Handler” named “OTHERS” is provided by PL/SQL

The handler OTHERS traps exceptions not handled in the other handlers in the exception handling section of the block

Thus in a program separate handlers are written for those named exceptions, that require some specific actions to be taken when the exception is fired while for all the remaining exceptions, the general OTHERS handler is used

Guidelines for Trapping Exceptions

WHEN OTHERS should be put up as the last handler

Handlers for several exceptions can be included in a single block but for a single exception only one handler can be written 

At a time, there can be only one active exception

Various Predefined Named Exceptions: Example

DECLARE

   myjob    emp.job%TYPE;

   emprec   emp%ROWTYPE;

BEGIN

   myjob := '&myJob';

   SELECT *

     INTO emprec

     FROM emp

    WHERE UPPER (job) = UPPER (myjob);

   DBMS_OUTPUT.put_line (emprec.ename || '     has salary    ' || emprec.sal);

EXCEPTION

   -- Various error handlers for predefined named exceptions

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line ('The job does not exist');

   WHEN TOO_MANY_ROWS

   THEN

      DBMS_OUTPUT.put_line ('More than one employee with the job');

   /* The handler OTHERS traps exceptions not handled in the other handlers in the exception handling section*/

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Other error ');

END;

/

Functions for Trapping Error Code and Message

There are two built-in functions which can be used to find out which exception occurred during an execution of a program unit

SQLCODE

          Returns the error number of the corresponding Oracle error that was raised

          Returns 1 for user-defined exception

SQLERRM

          Returns the error message associated with the given error number

          Returns “USER DEFINED EXCEPTION” for user-defined exceptions

SQLCODE and SQLERRM: Example

 CREATE TABLE ERRORS (

  ercode NUMBER,

  ermessage VARCHAR2(512));

DECLARE

   v_code   NUMBER;

   v_errm   VARCHAR2 (512);

   myno     NUMBER (1);

BEGIN

   myno := '&myNo';

EXCEPTION

   WHEN OTHERS

   THEN

      v_code := SQLCODE;

      v_errm := SQLERRM (SQLCODE);

      DBMS_OUTPUT.put_line (SQLERRM);

      INSERT INTO ERRORS

           VALUES (v_code, v_errm);

END;

/

Note: SQLCODE and SQLERRM being procedural statements can not be directly used inside SQL statement; hence their values are assigned to variables first and then used in INSERT statement

Unnamed Oracle Exceptions

Unnamed exceptions can be trapped by using OTHERS handler

Unnamed exceptions can be handled by PRAGMA EXCEPTION_INIT which tells the

compiler to associate an exception name with an oracle error number

PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL block is executed

PRAGMA EXCEPTION_INIT: Example

DECLARE

   e_emp_exist   EXCEPTION;

   --Naming  non-predefined oracle error

   PRAGMA EXCEPTION_INIT (e_emp_exist, -2292);

   v_deptno      dept.deptno%TYPE   := &dno;

BEGIN

   DELETE FROM dept

         WHERE deptno = v_deptno;

   COMMIT;

EXCEPTION

   WHEN e_emp_exist

   THEN

      DBMS_OUTPUT.put_line ('Can not delete dept      Employees exist');

END;

User-defined Exceptions

Declared in the Declare Section with data type as EXCEPTION

Raised Explicitly by using the RAISE keyword

Handled in the Exception Section

Local to a PL/SQL block

User-defined Exception: Example

DECLARE

   -- Declaring user defined exception

   e_check_sal   EXCEPTION;

   v_empno       emp.empno%TYPE   := &empno;

   emp_sal       emp.sal%TYPE     := &empsal;

   mgr_sal       emp.sal%TYPE;

BEGIN

   SELECT e1.sal

     INTO mgr_sal

     FROM emp e, emp e1

    WHERE e.mgr = e1.empno AND e.empno = v_empno;

   IF (emp_sal > mgr_sal)

   THEN

      RAISE e_check_sal;                    -- Raising user-defined exception

   ELSE

      UPDATE emp

         SET sal = emp_sal

       WHERE empno = v_empno;

   END IF;

   DBMS_OUTPUT.put_line ('Salary updated');

   COMMIT;

EXCEPTION

   --Handling user defined exception

   WHEN e_check_sal

   THEN

      DBMS_OUTPUT.put_line

                    ('Employee salary can not be greater than Manager salary');

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line (' Wrong  input');

END;

Exception Propagation

    Exceptions Raised in the Executable Section

   PL/SQL uses the following rule to determine which exception handler to invoke:

Current block has a handler for the exception, execute it and complete the block successfully. Control then passes to the enclosing block.

No handler for current exception, propagate the exception by raising it in the enclosing block. Step 1 is executed for the enclosing block. If there is no enclosing block, the exception will be propagated out to the calling environment, such as SQL* Plus.

Exceptions Raised in the Executable Section: Example 1

Exceptions Raised in the Executable Section: Example 2

Exceptions Raised in Declarative Section: Example 1

Exceptions Raised in Declarative Section:Example 2

Exceptions Raised in Exception Section:Example 1

Example – Exception Propagation

BEGIN

   UPDATE dept

      SET loc = 'New1'

    WHERE deptno = 10;

   DECLARE

      myjob    emp.job%TYPE   := '&myjob';

      emprec   emp%ROWTYPE;

   BEGIN

      UPDATE dept

         SET loc = 'New2'

       WHERE deptno = 20;

      SELECT *

        INTO emprec

        FROM emp

       WHERE job = UPPER (myjob);

      DBMS_OUTPUT.put_line (emprec.ename || ' has salary  ' || emprec.sal);

   EXCEPTION

      WHEN NO_DATA_FOUND

      THEN

         DBMS_OUTPUT.put_line ('The job does not exist');

      WHEN TOO_MANY_ROWS

      THEN

         DBMS_OUTPUT.put_line ('More than one employee with the job');

      WHEN OTHERS

      THEN

         DBMS_OUTPUT.put_line ('Other error   ' || SQLERRM);

   END;

   UPDATE dept

      SET loc = 'New3'

    WHERE deptno = 30;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

         ('The inner block error is trapped here as raised in

                                                     DECLARE section'

         );

END;