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

Database Interaction with PL/SQL: Nested Blocks in Depth

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.

  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
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
July 12, 2005

print this article



Please note that all 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 the documentation for the version you are using, if any of the programs failed to execute.

The problem of using Nested blocks

The seventh part of my series (previous article) introduced nested blocks in PL/SQL. Let us look into another frequent problem when using nested blocks in PL/SQL. Let us consider the following example:

  v_empno emp.empno%Type := &empno1;

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


If you carefully observe the above program, there exists a variable called 'v_empno' declared both in parent and nested blocks. Normally, a variable cannot be repeated in declarations. But the above program has those declarations in two different declaration sections which are not related to one another. These types of scenarios are acceptable in nested blocks.

If you execute the above program, it accepts two values (employee numbers) from the user. The first employee number gets stored in 'v_empno' of the parent block and the second in 'v_empno' of the second block. Now the interesting issue is the situation related to the SELECT statement. Don't forget that all parent block variables are accessible in child (nested) blocks. This means that the nested block can logically have the access for three variables namely 'v_ename', 'v_empno' (of nested block) and again 'v_empno' (of parent block). Now the SELECT statement considers which variable (is it the 'v_empno' of the nested block or the 'v_empno' of the parent block?) in its WHERE condition!

Any block always gives first preference to its own local variables. If it could not find the respective variable in its own block, it tries to search in its parent block. If the parent block defines that variable, it directly accesses it. If the parent block does not define that variable, it would be an error. So, in this case the variable in the nested block overrides the variable in the parent block. Finally, the SELECT statement in the above program always considers the second employee number (which gets stored in 'v_empno' of the nested block).

After reading the above three paragraphs, you should be convinced that local variables will be given first preference in access. So far, so good. But, what if I wanted to access the same variable ('v_empno') in the parent block within the nested block (or child block)? The next section gives you the solution.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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