Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors

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

Handling and Re-raising an exception

Parts seven and eight of my series gave several tips for using nested blocks with exceptions. Before going into a new concept, I would like to introduce one more tip for using exceptions with nested blocks. Let us consider the following example:

<<parent>>
Declare
  v_empno emp.empno%Type;
Begin

  <<nested1>>
  Declare
    v_empno emp.empno%type := &empno1;
    v_ename emp.ename%Type;
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno;
    dbms_output.put_line('Name:'||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

  <<nested2>>
  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('Name:'||v_ename);
  Exception
    When no_data_found then
      parent.v_empno := v_empno;
      raise;
  End;

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

Even though the program looks bit lengthy, there exists nothing new, except the word 'raise' without any exception. As explained in my previous articles, RAISE simply raises a specified exception, that could be either pre-defined or user-defined. We handle that exception within the exception handler section. But here, I used RAISE without specifying any exception. That means it should re-RAISE the current exception (even though it is being handled). In the above program, the NO_DATA_FOUND exception is already being handled within the nested blocks. But, you can observe that I am not displaying the message straight away. I am just storing the EMPNO into the parent block related variable (v_empno) and re-raising the exception.

When the exception gets re-raised, it searches for another exception handler within the same block or its parent block (but not its paralleled block, like 'nested2'). And, in this case, the parent block automatically catches that re-raised exception and handles it by displaying a message to the user along with the 'empno'. In this way we can maintain a mechanism for handling exceptions centrally situated at a single location (rather than scattering everywhere).