Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Thursday, 7 April 2016

Oracle 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


Types of 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 Exceptions

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 1


Exceptions Raised in the Executable Section: Example 2


Exceptions Raised in the Executable Section: Example 2


Exceptions Raised in Declarative Section: Example 1

Exceptions Raised in Declarative Section: Example 1


Exceptions Raised in Declarative Section:Example 2

Exceptions Raised in Declarative Section:Example 2

Exceptions Raised in Exception Section:Example 1


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;
Share this article :

0 comments:

Post a Comment