EXCEPTION handling in both parent and nested PL/SQL blocks - Oracle

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.