Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL: Nested Blocks in Depth

EXCEPTION handling in both parent and nested PL/SQL blocks - Oracle

This is part eight of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to user defined exceptions and nested blocks in PL/SQL. In this article, we will look into handling more than one exception and different tips on using nested blocks.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Nested Blocks in Depth
  2. Labeling the PL/SQL blocks
  3. EXCEPTION handling in both parent and nested PL/SQL blocks
  4. SQLCODE and SQLERRM
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
July 12, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Until now we have seen exception handling either at the parent block or at the child block. It is possible to handle exceptions even at both levels. The statements at the parent block may give raise to the exception handler at the parent block; the case is similar at the child block. The following example demonstrates this.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_sal2 emp.empno%Type := &sal2;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where sal=v_sal2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_sal2);
  End;

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
End;

From the above program, if the first SELECT could not retrieve any row, the exception NO_DATA_FOUND gets handled at the parent block, and I hope you can assume that the second SELECT works with the exception handler within the nested block. The above is only for demonstration purposes. It is better to implement two separate paralleled nested blocks in the above case (as covered in the previous part of my article).

Now let us consider another situation. If the second SELECT (in the nested block) tries to retrieve more than one row (as more than one employee may exist with the same salary), it raises a TOO_MANY_ROWS exception. But I didn't handle it in either of the blocks. But we can handle it even at the parent block (not only at child block). This means, if an exception gets raised at the child block, and if the PL/SQL runtime does not find any exception handler at the child block, it carries the same exception to the parent block and tries to find the handler there. If it finds an exception handler at the parent block for the exception raised at the child block (and not handled within the child block), it gets successfully handled at the parent block. This is demonstrated in the following example.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_sal2 emp.empno%Type := &sal2;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line('Name: '||v_ename1);

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where sal=v_sal2;
    dbms_output.put_line('Name:'||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_sal2);
  End;

Exception
  When no_data_found then
    dbms_output.put_line('Employee not found with '||v_sal1);
  When too_many_rows then
    dbms_output.put_line('More number of employees are found either with ' || v_sal1 || ' or ' || v_sal2);
End;

If you observe the TOO_MANY_ROWS exception section of the parent block, the message being given to the user is a general message (but not very specific). But it handles the TOO_MANY_ROWS exceptions raised at both the parent and child blocks. It would be more efficient to used parallel nested blocks in the above situation. Again, this is only for demonstration.

Generally it is a good practice, to handle the OTHERS exception at the parent block to handle any sort of error, and provide a generic message to the user, rather than an abnormal termination of the program.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: