Home arrow Oracle arrow Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

This is part nine of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at different tips for using nested blocks together with exceptions. In this article, we will see how to handle exceptions centrally and have a look at cursors; we will also receive an introduction to the SQL cursor.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors
  2. Introduction to Cursors
  3. The powerful SQL cursor
  4. %NOTFOUND, %ROWCOUNT and %ISOPEN attributes
By: Jagadish Chatarji
Rating: starstarstarstarstar / 42
July 19, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Please note that all of the examples in this series have been tested only with Oracle 10g. I didn't really test them with all the previous versions of Oracle. I suggest you to refer to the documentation of the respective version you are using, if any of the programs fail to execute.

Handling and Re-raising an exception

Parts seven and eight of my series gave several tips for using nested blocks with exceptions. Before going into a new concept, I would like to introduce one more tip for using exceptions with nested blocks. Let us consider the following example:

<<parent>>
Declare
  v_empno emp.empno%Type;
Begin

  <<nested1>>
  Declare
    v_empno emp.empno%type := &empno1;
    v_ename emp.ename%Type;
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno;
    dbms_output.put_line('Name:'||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

  <<nested2>>
  Declare
    v_empno emp.empno%type := &empno2;
    v_ename emp.ename%Type;
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno;
    dbms_output.put_line('Name:'||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

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

Even though the program looks bit lengthy, there exists nothing new, except the word 'raise' without any exception. As explained in my previous articles, RAISE simply raises a specified exception, that could be either pre-defined or user-defined. We handle that exception within the exception handler section. But here, I used RAISE without specifying any exception. That means it should re-RAISE the current exception (even though it is being handled). In the above program, the NO_DATA_FOUND exception is already being handled within the nested blocks. But, you can observe that I am not displaying the message straight away. I am just storing the EMPNO into the parent block related variable (v_empno) and re-raising the exception.

When the exception gets re-raised, it searches for another exception handler within the same block or its parent block (but not its paralleled block, like 'nested2'). And, in this case, the parent block automatically catches that re-raised exception and handles it by displaying a message to the user along with the 'empno'. In this way we can maintain a mechanism for handling exceptions centrally situated at a single location (rather than scattering everywhere).



 
 
>>> 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: