Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks - The solution
(Page 3 of 5 )
The following program solves the problem explained in the previous section:
Declare
v_SearchingEmpno emp.empno%type;
v_empno1 emp.empno%Type := &empno1;
v_ename emp.ename%Type;
v_empno2 emp.empno%Type := &empno2;
Begin
v_SearchingEmpno := v_empno1;
Select ename into v_ename
From emp Where empno=v_empno1;
dbms_output.put_line('First Employee Name: '||v_ename);
v_SearchingEmpno := v_empno2;
Select ename into v_ename
From emp Where empno=v_empno2;
dbms_output.put_line('Second Employee Name: '||v_ename);
Exception
When no_data_found then
dbms_output.put_line('Employee not found with '||v_SearchingEmpno);
End;
The magic is in using a temporary variable (v_SearchingEmpno) to hold the employee number, which is being fetched through the SELECT statement. I stored ‘v_empno1’ into ‘v_SearchingEmpno’ before it is searched for (or fetched). If the first SELECT fails, the exception handler displays the value available in ‘v_SearchingEmpno’ (which is nothing but ‘v_empno1’). The same thing happens with ‘v_empno2’ as well.
But still there is a minor problem. Even though the above program gives the details of the invalid employee number to the user, it doesn't give you both! Which means, if I provide wrong employee numbers for both 'v_empno1' and 'v_empno2', only one gets reported to the user.
And another problem is that, if I provide an invalid value to the first employee number and a valid value to the second employee number, we would never get the details of the second employee (even though the second employee number is a valid one). The next section addresses these issues using nested blocks in PL/SQL (a new concept!).
Next: The most efficient solution >>
More Oracle Articles
More By Jagadish Chatarji
/ 24 
