Exception Handling in PL/SQL

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 (terminate with success) i. e. the effect of the DML operations performed before the exception is raised, can be saved else the earlier operations are rolled back (terminate with failure)

Types of Exceptions 

a) Named Exceptions:

The exceptions which are already given names by Oracle are declared in the STANDARD package in PL/SQL. You do not have to declare them in your own programs. You only need to handle them.

¢Oracle has around 20 named predefined  exceptions

Trapped within the Exception handling block

Oracle Exception Name -Oracle Error  -       Explanation

1.NO_DATA_FOUND                 ORA-01403      - You executed a SELECT INTO statement and no rows were returned.

2. TOO_MANY_ROWS              ORA-01422       -You tried to execute a SELECT INTO  statement and more than one row  was returned.

3. ZERO_DIVIDE                      ORA-01476      -You tried to divide a number by zero.

4. INVALID_NUMBER                 ORA-01722       -You tried to execute an SQL statement that tried to convert a string to a number, but it was unsuccessful.

Example:

SQL> insert into emp(empno, ename) values('ppp','ppp');

insert into emp(empno, ename) values('ppp','ppp')

                                     *

ERROR at line 1:

ORA-01722: invalid number

5.. VALUE_ERROR                    ORA-06502     -You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data.

6. CURSOR_ALREADY_OPEN      ORA-06511      -You tried to open a cursor that is already open

b) UnNamed Exceptions:

It is used to trap an internal error raised by PL/SQL or the SQL engine, which is not been given a predefined name. Although this error is identified only by its internal error number, exception handlers need a name by which they can check for a match .The following are some of the unnamed exceptions which don’t have standard Oracle names but the names can be created and assigned with the error number by the user by using PRAGMA EXCEPTION_INIT

1. ORA-00001: unique constraint (SCOTT.SYS_C003701) violated   - raised when trying to put a duplicate value in a column that has UNIQUE / PRIMARY KEY constraint

2. ORA-02292: integrity constraint (SCOTT.SYS_C003731) violated - child record Found – raised when trying to delete a parent record that has  child records and while setting the referential integrity constraint in the referencing table no “ON DELETE” clause used 

Named Exceptions	Set of Oracle    standard exceptions that are already named	Do not declare and allow the Oracle server to raise them implicitly
Unnamed Exceptions	Set of Oracle standard exceptions that are not given name	Declare within the declarative section. Associate an exception name with an Oracle error number and allow Oracle to raise them implicitly.
UserDefined Exceptions	A condition that the developer determines is        abnormal	Declare within the        declarative section and           raise explicitly

e.g. # 1

DECLARE

      v_emp_rec  emp%ROWTYPE;

BEGIN

    SELECT * INTO v_emp_rec FROM emp WHERE empno=1001;

    DBMS_OUTPUT.PUT_LINE(‘Employee Number       :’ || v_emp_rec.empno);

    DBMS_OUTPUT.PUT_LINE(‘Employee Name          :’ || v_emp_rec.ename);

    DBMS_OUTPUT.PUT_LINE(‘Employee Salary         :’ || v_emp_rec.sal);

    DBMS_OUTPUT.PUT_LINE(‘Employee Deptno        :’ || v_emp_rec.deptno);

EXCEPTION

    WHEN NO_DATA_FOUND THEN

              DBMS_OUTPUT.PUT_LINE(‘Employee Does Not Exist’);

END;

e.g. # 2

DECLARE

      v_emp_rec  emp%ROWTYPE;

BEGIN

    SELECT * INTO v_emp_rec FROM emp;

    DBMS_OUTPUT.PUT_LINE(‘Employee Number       :’ || v_emp_rec.empno);

    DBMS_OUTPUT.PUT_LINE(‘Employee Name          :’ || v_emp_rec.ename);

    DBMS_OUTPUT.PUT_LINE(‘Employee Salary         :’ || v_emp_rec.sal);

    DBMS_OUTPUT.PUT_LINE(‘Employee Deptno        :’ || v_emp_rec.deptno);

EXCEPTION

    WHEN TOO_MANY_ROWS  THEN

           DBMS_OUTPUT.PUT_LINE(‘Too Many Employees’);

END;

/

Raising an Exception

1.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

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

¢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;

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;

--EXECUTION

SQL> /

Enter value for eno: 7839

old   4: SELECT ename INTO myName FROM emp WHERE empno = &eno;

new   4: SELECT ename INTO myName FROM emp WHERE empno = 7839;

Name of the employee :  KING

PL/SQL procedure successfully completed.

SQL> /

Enter value for eno: 4545

old   4: SELECT ename INTO myName FROM emp WHERE empno = &eno;

new   4: SELECT ename INTO myName FROM emp WHERE empno = 4545;

WRONG EMPLOYEE NUMBER

PL/SQL procedure successfully completed.

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('Sorry Boss! Something went wrong :( ‘);

END;

To test various exception handlers give calls to the block passing different value of job each time

SQL> /

Enter value for myjob: CLERK

old   5:  myJob :='&myJob';

new   5:  myJob :='CLERK‘;

More than one employee with the job

PL/SQL procedure successfully completed.

SQL> /

Enter value for myjob: Secretary

old   5:  myJob :='&myJob';

new   5:  myJob :=‘Secretary';

The job does not exist

PL/SQL procedure successfully completed.

SQL> /

Enter value for myjob: Assistant Manager

old   5:  myJob :='&myJob';

new   5:  myJob :=' Assistant Manager';

Other error 

(ORA-06502: PL/SQL: numeric or value error: character string buffer too small)

PL/SQL procedure successfully completed.

-- Call with correct value

SQL> /

Enter value for myjob: PRESIDENT

old   5:  myJob :='&myJob';

new   5:  myJob :='PRESIDENT';

KING     has salary    8734

PL/SQL procedure successfully completed.

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), who varchar2(30) , when date);

DECLARE

            v_code  NUMBER;

            v_errm VARCHAR2(512);

            myNo NUMBER(1);

 BEGIN

      myNo := '&myNo';

 EXCEPTION

      WHEN OTHERS THEN

                    v_code := SQLCODE;

                    v_errm := SQLERRM;

                    DBMS_OUTPUT.PUT_LINE(SQLERRM);

                    INSERT INTO errors VALUES(v_code, v_errm, user, sysdate);

 END;

/

  Note that SQLCODE and SQLERRM can not be directly used inside SQL statement; hence their values are assigned to variables first and then used in INSERT statement.Maximum size of Oracle error message is 512 characters.

SQL> /

Enter value for myno: 45

old   6:     myNo := '&myNo';

new   6:     myNo := '45';

ORA-06502: PL/SQL: numeric or value error: number precision too large

PL/SQL procedure successfully completed.

SQL> /

Enter value for myno: Meenal

old   6:     myNo := '&myNo';

new   6:     myNo := 'Meenal';

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

PL/SQL procedure successfully completed.

SELECT * FROM errors;

 ERCODE ERMESSAGE

--------- ----------------------------------------------------------------------

    -6502 ORA-06502: PL/SQL: numeric or value error: number precision too large

    -6502 ORA-06502: PL/SQL: numeric or value error: character to number conversion error 

Exception Handling in PL/SQL

¢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

¢In Oracle PL/SQL,  PRAGMA  refers to a compiler directive or "hint" 

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

¢PRAGMA  is used to provide an instruction to the compiler. The 5 types of Pragma directives available in Oracle are listed below:

* PRAGMA AUTONOMOUS_TRANSACTION
* PRAGMA SERIALLY_REUSABLE
* PRAGMA RESTRICT_REFRENCES
* PRAGMA EXCEPTION_INIT
* PRAGMA INLINE

Associating a User Defined Exception with an Error Number (or Exception Code)

A user defined exception can be associated with an error number using PRAGMA EXCEPTION_INIT. The pragma is a compiler directive which hints the compiler to accept the directions provided in the program. The PRAGMA EXCEPTION_INIT directs the compiler to align the user defined exception with a self assigned error number. The error number must be one of the valid ORA error codes, which are defined by the server.

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 ’|| SQLERRM);

  END;

To demonstrate the working of the above program you have to verify that there is a primary key constraint on deptno column of dept table and referential integrity constraint on deptno column of the emp table with deptno column of dept table as its referenced column

For Oracle predefined exceptions the error numbers are negative except for NO_DATA_FOUND (+100). The error number for a specific oracle exception can be found from the oracle documentation

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

The error code  -2292 given in above example, is for the exception that is fired by oracle when one is trying to delete a parent record that has child records. This exception does not have a name. So we can not write a separate handler for it as the handler syntax needs exception name. Hence in the above program we are associating a name with this Oracle exception using the PRAGMA EXCEPTION_INIT so that we can then write a separate handler for this exception using the name associated with it. However note that this name association is local and is limited only to the above code block.

We can also locally rename the predefined named exceptions to another name that may be more relevant for our program, using PRAGMA EXCEPTION_INIT

User-defined Exception

¢Declared in the Declare Section with data type as EXCEPTION

¢Raised Explicitly by using the RAISE keyword

¢Local to a PL/SQL block

User-defined Exception: Example

DECLARE

   e_myException EXCEPTION;

   vn1 NUMBER:=10;

   vn2 NUMBER:=5;

BEGIN

   IF vn1+vn2>=12 THEN

   RAISE e_myException;

   ELSE

   DBMS_OUTPUT.PUT_LINE('no problem');

   END IF;

EXCEPTION

   WHEN OTHERS THEN

   DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);

END;

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/Some other Error’);

END;

Consider the following business rule

Revised Company policy imposed the rule that the salary of employee should not be greater than his manager’s salary. So for every update in salary the above checking is done using the user defined exception e_check_Sal.

If any violation, then error message is given else the salary is updated.

In the above program we are dealing with only those employees that have a manager.

Exception Propagation

¢Exceptions Raised in the Executable Section

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

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

2.  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.

¢The figure below shows the program flow, which is followed when an exception situation occurs in the PL/SQL block and the exception has been handled. Once the exception has been raised and trapped, and the control moves to EXCEPTION section, the program propagates in the forward direction.

¢If the exception has not been handled, the program terminates abruptly or propagates to the calling environment.

¢Fig 1a: Program flow when the Exception has been trapped and handled

¢Fig 1b: Program flow when the Exception has not been handled

EXAMPLE:

declare

   e_myexception exception;

   a number:=10;

   b number:=5;

begin

   if a+b>=12 then

   raise xe_myexception;

   else

   dbms_output.put_line('no problem');

   end if;

exception

   when others then

   dbms_output.put_line(sqlcode||' '||

sqlerrm);

end;/

output :

1 User-Defined Exception