Handling PL/SQL Errors

How do we avoid abnormal termination of code in ORACLE? What are the different ways of handling Exceptions?

Program flow when the Exception has not been handled

Program flow when the Exception has been trapped and handled

In this section, we will discuss about the following,

1) What is Exception Handling.
2) Structure of Exception Handling.
3) Types of Exception Handling.

1) What is Exception Handling?

PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly.

PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A Message 

This error message and code can be identified by SQLCODE and SQLERRM.

2) Structure of Exception Handling.

General Syntax for coding the exception section

 DECLARE

   Declaration section

 BEGIN

   Exception section

 EXCEPTION

 WHEN Ex_Name1 THEN

    -Error handling statements

 WHEN Ex_Name2 THEN

    -Error handling statements

 WHEN Others THEN

   -Error handling statements

END;

General PL/SQL statments can be used in the Exception Block.

When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example, in the above example, if the error raised is 'Ex_Name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not Explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled.

If there are nested PL/SQL blocks like this.

 DELCARE

   Declaration section

 BEGIN

    DECLARE

      Declaration section

    BEGIN

      Execution section

    EXCEPTION

      Exception section

    END;

 EXCEPTION

   Exception section

 END;

In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error.

3) Types of Exception.

There are 3 types of Exceptions.
a) Named System Exceptions
b) Unnamed System Exceptions
c) User-defined Exceptions

a) Named System Exceptions

System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions.

For example: NO_DATA_FOUND and ZERO_DIVIDE are called Named System exceptions.

Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.

Exception Name	Reason	Error No
CURSOR_ALREADY_OPEN	When you open a cursor that is already open.	ORA-06511
INVALID_CURSOR	When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.	ORA-01001
NO_DATA_FOUND	When a SELECT...INTO clause does not return any row from a table.	ORA-01403
TOO_MANY_ROWS	When you SELECT, or fetch more than one row into a record or variable.	ORA-01422
ZERO_DIVIDE	When you attempt to divide a number by zero.	ORA-01476

For Example: Suppose a NO_DATA_FOUND exception is raised in a proc, we can write a code to handle the exception as given below.

BEGIN

  Execution section

EXCEPTION

WHEN NO_DATA_FOUND THEN

Dbms_Output.Put_Line ('A SELECT...INTO did not return any row.'); END;

END;

Consider Below Example-

We will be using below table in this example-

SQL> SELECT * FROM DEPT_TEST;

    DEPTNO DEPARTMENT
---------- --------------------
        30 Security
        10 ENU
        20 Insurance
        40 Retail
        50 BFSI
        60 CISCO
        80 Accounts
        70 Finance

8 rows selected.

DECLARE

V_ID DEPT_TEST.DEPTNO%TYPE:=80;

V_DEPT DEPT_TEST.DEPARTMENT%TYPE;

BEGIN

Select DEPARTMENT Into V_DEPT

From DEPT_TEST

Where DEPTNO=V_ID;

DBMS_OUTPUT.PUT_LINE ('Department Name: '||
V_DEPT);

END;



IF we Execute above got Oracle will throw NO_DATA_FOUND Error
as Department 80 doesn’t Exist. Now, instead of executing abruptly we can
handle this error and display some message as shown in the below modified Block
(Exception Block added).

SQL> DECLARE
  2  V_ID DEPT_TEST.DEPTNO%TYPE:=90;
  3  V_DEPT DEPT_TEST.DEPARTMENT%TYPE;
  4  BEGIN
  5  Select DEPARTMENT Into V_DEPT
  6  From DEPT_TEST
  7  Where DEPTNO=V_ID;
  8  DBMS_OUTPUT.PUT_LINE ('Department Name: '|| V_DEPT);
  9  EXCEPTION
 10  WHEN NO_DATA_FOUND Then
 11    DBMS_OUTPUT.PUT_LINE('DEPARTMENT is not yet mapped');
 12  WHEN OTHERS Then
 13    DBMS_OUTPUT.PUT_LINE(SQLERRM);
 14  END;
 15  /
DEPARTMENT is not yet mapped

PL/SQL procedure successfully completed.

b) Unnamed
System Exceptions

Those
system exceptions for which oracle does not provide a name is known as unnamed
system exception. These exceptions do not occur frequently. These Exceptions
have a code and an associated message.

There are
two ways to handle unnamed sysyem exceptions: 

1. By using the
WHEN OTHERS exception handler, or 

2. By associating the exception code to a name and using it as a named
exception.

We can
assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT.

EXCEPTION_INIT will associate a
predefined Oracle error number to a programmer_defined exception name.

Steps to
be followed to use unnamed system exceptions are 

• They are
raised implicitly. 

• If they are not handled in WHEN Others they must be handled explicity. 

• To handle the exception explicitly, they must be declared using Pragma
EXCEPTION_INIT as     given above and
handled referencing the user-defined exception name in the exception section.

The
general syntax to declare unnamed system exception using EXCEPTION_INIT is:

DECLARE 

   Exception_name EXCEPTION; 

   PRAGMA 

   EXCEPTION_INIT (Exception_name,
Err_code); 

BEGIN 

Execution section

EXCEPTION

  WHEN Exception_name THEN

     Handle the exception

END;

For Example: Let’s
consider the Product table and Order_Items table from SQL joins. Here
Product_id is a primary key in Product Table and a Foreign key in Order_Items
table.  If we try to delete a Product_id from the product table when it
has child records in Order_id table an exception will be thrown with oracle
code number -2292. 

We can provide a name to this exception and handle it in the exception section
as given below.

 DECLARE


  Child_rec_exception EXCEPTION; 

  PRAGMA 

   EXCEPTION_INIT
(Child_rec_exception, -2292); 

BEGIN 

  Delete FROM product where product_id= 104; 

EXCEPTION 

   WHEN Child_rec_exception 

   THEN Dbms_output.put_line('Child records are
present for this product_id.'); 

END; 



Another Example-




In the PL/SQL block below, EMP_TEST (DEPTNO Foreign Key) and
DEPT_TEST (DEPTNO Unique Key) 

SQL> SELECT DISTINCT DEPTNO FROM EMP_TEST;

    DEPTNO
----------
        30
        20
        10

SQL> SELECT DEPTNO FROM DEPT_TEST;

    DEPTNO
----------
        30
        10
        20
        40
        50
        60
        80
        70

8 rows selected.

Here, you can handle an Exception which will check for Department
child records in EMP_TEST before deleting it from DEPT_TEST.

SQL> DECLARE
  2  Child_rec_exception EXCEPTION;
  3  PRAGMA EXCEPTION_INIT(Child_rec_exception, -2292);
  4  BEGIN
  5  DELETE FROM DEPT_TEST Where DEPTNO=10;
  6  DBMS_OUTPUT.PUT_LINE('Department Deleted From Parent Table- No Child Record Found');
  7  EXCEPTION
  8  WHEN Child_rec_exception Then
  9  DBMS_OUTPUT.PUT_LINE('Child Records present for this Department');
 10  END;
 11  /
Child Records present for this Department

PL/SQL procedure successfully completed.

SQL> DECLARE
  2  Child_rec_exception EXCEPTION;
  3  PRAGMA EXCEPTION_INIT(Child_rec_exception, -2292);
  4  BEGIN
  5  DELETE FROM DEPT_TEST Where DEPTNO=60;
  6  DBMS_OUTPUT.PUT_LINE('Department Deleted From Parent Table- No Child Record Found');
  7  EXCEPTION
  8  WHEN Child_rec_exception Then
  9  DBMS_OUTPUT.PUT_LINE('Child Records present for this Department');
 10  END;
 11  /
Department Deleted From Parent Table- No Child Record Found

PL/SQL procedure successfully completed.

c)
User-defined Exceptions 



Apart
from system exceptions we can explicitly define exceptions based on business
rules. These are known as user-defined exceptions.



Steps
to be followed to use user-defined exceptions: 



• They should be explicitly declared in the declaration
section. 

• They should be explicitly raised in the Execution Section. 

• They should be handled by referencing the user-defined exception
name in the exception section.





How to Define
Exception



Declare Exception

You must have to declare user defined exception name in
DECLARE block.

User_Define_Exception_Name EXCEPTION;



Exception and Variable both are same way declaring but exception
use for store error condition not a storage item.






RAISE exception

RAISE statement to raised defined exception name and control
transfer to an EXCEPTION block.

RAISE User_Define_Exception_Name;



Implement exception condition

In PL/SQL EXCEPTION block add WHEN condition to implement
user define action.

WHEN User_Define_Exception_Name THEN

                       User
defined statement (action) will be taken;



DECLARE

                       User_Define_Exception_Name
EXCEPTION;

BEGIN

                       statement(s);

                       IF condition THEN

                                              RAISE
User_Define_Exception_Name;

                       END IF;

EXCEPTION

                       WHEN
User_Define_Exception_Name THEN

                                              User
defined statement (action) will be taken;                     

END;

SQL> SELECT * FROM EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

Let’s define an
Exception which will check that salary inserted should not be less than the
minimum limit set.



DECLARE

     Low_Sal_Exc EXCEPTION;

     Min_sal NUMBER:= 8000;

     New_Sal NUMBER:= 4700; /*Change this for second Execution to 23000*/

    BEGIN

     INSERT INTO EMP_TEMP(ID, NAME, DEPTNO,
SALARY)

                      VALUES
(108,'Tom',30,New_Sal);



     IF New_Sal < Min_Sal THEN

        RAISE Low_Sal_Exc;

    END IF;

    DBMS_OUTPUT.PUT_LINE('Record Inserted
Successfully');



    Commit;

   EXCEPTION

   WHEN Low_Sal_Exc THEN

      Rollback;

      DBMS_OUTPUT.PUT_LINE ('Salary is less
than '||Min_Sal);

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE (SQLERRM);

END;

SQL> DECLARE
  2       Low_Sal_Exc EXCEPTION;
  3       Min_sal NUMBER:= 8000;
  4       New_Sal NUMBER:= 4700;
  5      BEGIN
  6       INSERT INTO EMP_TEMP(ID, NAME, DEPTNO, SALARY)
  7                        VALUES (108,'Tom',30,New_Sal);
  8
  9       IF New_Sal < Min_Sal THEN
 10          RAISE Low_Sal_Exc;
 11      END IF;
 12      DBMS_OUTPUT.PUT_LINE('Record Inserted Successfully');
 13
 14      Commit;
 15     EXCEPTION
 16     WHEN Low_Sal_Exc THEN
 17        Rollback;
 18        DBMS_OUTPUT.PUT_LINE ('Salary is less than '||Min_Sal);
 19     WHEN OTHERS THEN
 20        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 21  END;
 22  /
Salary is less than 8000

PL/SQL procedure successfully completed.

SQL> DECLARE
  2       Low_Sal_Exc EXCEPTION;
  3       Min_sal NUMBER:= 8000;
  4       New_Sal NUMBER:= 23000;
  5      BEGIN
  6       INSERT INTO EMP_TEMP(ID, NAME, DEPTNO, SALARY)
  7                        VALUES (108,'Tom',30,New_Sal);
  8
  9       IF New_Sal < Min_Sal THEN
 10          RAISE Low_Sal_Exc;
 11      END IF;
 12      DBMS_OUTPUT.PUT_LINE('Record Inserted Successfully');
 13
 14      Commit;
 15     EXCEPTION
 16     WHEN Low_Sal_Exc THEN
 17        Rollback;
 18        DBMS_OUTPUT.PUT_LINE ('Salary is less than '||Min_Sal);
 19     WHEN OTHERS THEN
 20        DBMS_OUTPUT.PUT_LINE (SQLERRM);
 21  END;
 22  /
Record Inserted Successfully

PL/SQL procedure successfully completed.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. 

So, leave your thoughts before you leave the page.