Labeling the PL/SQL blocks - Oracle

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;