Database Interaction with PL/SQL: Nested Blocks in Depth

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:

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

  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('Empno: '||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;

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.