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

Labeling the 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

Here, I provide the solution for the problem defined in the previous section, using the concept of labeling the PL/SQL blocks. Consider the following example:

<<parent>>
Declare
  v_empno emp.empno%Type := &empno1;
Begin

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

End;

The '<<parent>>' label performs the whole magic. That is how we label a block. Even the nested block in the above program got labeled with '<<child>>'. Actually, it is not necessary to label a child block in this case. But, I did label it, just to inform you that you can also label child (nested) blocks in that manner.

The SELECT statement in the above program is also bit different (in WHERE condition). We are using 'parent.v_empno' to denote that we are trying to access the variable 'v_empno' of '<<parent>>' block. This notation itself makes it much clearer to PL/SQL runtime that it needs to access the variable of the parent block (but not the local variable of child block).

The most important issue to remember is that this notation (or labeling) works only with parent-child types of blocks but not paralleled blocks within the same parent block. The following example is INVALID as it tries to access the variable of its own adjacent block (but not parent block).

<<parent>>
Declare
  v_empno emp.empno%Type := &empno1;
Begin

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

  <<child2>>
  Declare
    v_ename emp.ename%type;
  Begin
    Select ename into v_ename
    From emp Where empno=child1.v_empno; --this is invalid
    dbms_output.put_line('Empno: '|| parent.v_empno); --this is valid
    dbms_output.put_line('Name: '||v_ename);
  Exception
    When no_data_found then
      dbms_output.put_line('Employee not found with '||v_empno);
  End;

End;



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