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.

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.

{mospagebreak title=Labeling the PL/SQL blocks}

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;

{mospagebreak title=EXCEPTION handling in both parent and nested PL/SQL blocks}

Until now we have seen exception handling either at the parent block or at the child block. It is possible to handle exceptions even at both levels. The statements at the parent block may give raise to the exception handler at the parent block; the case is similar at the child block. The following example demonstrates this.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_sal2 emp.empno%Type := &sal2;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line(‘Name: ‘||v_ename1);

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where sal=v_sal2;
    dbms_output.put_line(‘Name:’||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line(‘Employee not found with ‘||v_sal2);
  End;

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

From the above program, if the first SELECT could not retrieve any row, the exception NO_DATA_FOUND gets handled at the parent block, and I hope you can assume that the second SELECT works with the exception handler within the nested block. The above is only for demonstration purposes. It is better to implement two separate paralleled nested blocks in the above case (as covered in the previous part of my article).

Now let us consider another situation. If the second SELECT (in the nested block) tries to retrieve more than one row (as more than one employee may exist with the same salary), it raises a TOO_MANY_ROWS exception. But I didn’t handle it in either of the blocks. But we can handle it even at the parent block (not only at child block). This means, if an exception gets raised at the child block, and if the PL/SQL runtime does not find any exception handler at the child block, it carries the same exception to the parent block and tries to find the handler there. If it finds an exception handler at the parent block for the exception raised at the child block (and not handled within the child block), it gets successfully handled at the parent block. This is demonstrated in the following example.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_sal2 emp.empno%Type := &sal2;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line(‘Name: ‘||v_ename1);

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where sal=v_sal2;
    dbms_output.put_line(‘Name:’||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line(‘Employee not found with ‘||v_sal2);
  End;

Exception
  When no_data_found then
    dbms_output.put_line(‘Employee not found with ‘||v_sal1);
  When too_many_rows then
    dbms_output.put_line(‘More number of employees are found either with ‘ || v_sal1 || ‘ or ‘ || v_sal2);
End;

If you observe the TOO_MANY_ROWS exception section of the parent block, the message being given to the user is a general message (but not very specific). But it handles the TOO_MANY_ROWS exceptions raised at both the parent and child blocks. It would be more efficient to used parallel nested blocks in the above situation. Again, this is only for demonstration.

Generally it is a good practice, to handle the OTHERS exception at the parent block to handle any sort of error, and provide a generic message to the user, rather than an abnormal termination of the program.

{mospagebreak title=SQLCODE and SQLERRM}

Those two are called as error reporting functions in PL/SQL. SQLCODE and SQLERRM give the code and error message respectively. Those values can be directly used within the PL/SQL program during the handling of exceptions. The following example demonstrates this.

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line(‘Name: ‘||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line(‘Employee not found with ‘||v_sal1);
  When others then
    dbms_output.put_line(‘The program received an error. Error message returned was: ‘ || SQLCODE || ‘,’ || SQLERRM);

End;

The above example does not handle the TOO_MANY_ROWS exception. Indeed, it gets automatically handled with the OTHERS exception. Within that exception, we are displaying both our own message along with the message given by the system for that error. SQLERRM and SQLCODE are generally used in the OTHERS exception, when you really don’t know what type of exception gets raised during execution.

SQLERRM already includes SQLCODE within the message. So you need not use SQLCODE in the above scenario. But if you really want to use it, you can follow the example given below:

Declare
  v_sal1 emp.empno%Type := &sal1;
  v_ename1 emp.ename%Type;
Begin

  Select ename into v_ename1
  From emp Where sal=v_sal1;
  dbms_output.put_line(‘Name: ‘||v_ename1);

Exception
  When no_data_found then
    dbms_output.put_line(‘Employee not found with ‘||v_sal1);
  When others then
    if SQLCODE = -1422 then
      dbms_output.put_line(‘More than one employee exists with the same salary’);
    else
      dbms_output.put_line(‘The program received an error. Error message returned was: ‘ || SQLCODE || ‘,’ || SQLERRM);
    end if;

End;

In the above program -1422 is the SQL error code pre-assigned to the exception TOO_MANY_ROWS (which gets shown in the SQLERRM). And I hope the rest is same.

[gp-comments width="770" linklove="off" ]

chat