HomeOracle Page 3 - Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks
The solution - Oracle
This is part 7 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to exception handling and went through some predefined exceptions. In this article, we will focus on user defined exceptions in PL/SQL and some tips for working with exceptions efficiently.
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!).