Database Interaction with PL/SQL, Introduction to Cursors, Implicit Cursors (Page 1 of 4 )
This is part nine of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we looked at different tips for using nested blocks together with exceptions. In this article, we will see how to handle exceptions centrally and have a look at cursors; we will also receive an introduction to the SQL cursor.
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).
Next: Introduction to Cursors >>
More Oracle Articles
More By Jagadish Chatarji
/ 34 
