The solution - Oracle

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!).