A frequently faced problem in exception handling - Oracle

This is a very common situation, generally faced by any beginner, who tries to program using exception handling. Instead of explaining the problem, let us go through an example of the appropriate type of problem.

Declare
  v_empno1 emp.empno%Type := &empno1;
  v_ename emp.ename%Type;
  v_empno2 emp.empno%Type := &empno2;
Begin
  Select ename into v_ename
  From emp Where empno=v_empno1;
  dbms_output.put_line('First Employee Name: '||v_ename);

  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');
End;

The above program is very simple. It just accepts two employee numbers from the user and displays the names of those two. If you properly give the two employee numbers (which exist in database table), the program works fine.

Say I gave a wrong employee number for 'v_empno1'. The first SELECT statement fails and immediately jumps to the exception section (without even worrying about the second employee number). Even the message is generic (not showing which employee number it could not find). Say that now I gave a wrong employee number for 'v_empno2'. The second SELECT gets failed and jumps to the exception section.

From the above explanation, we can deduce that we are unable to provide proper information (in the form of message) to the user about the invalid employee number. Instead of providing a generic message, I would also like to add the invalid employee number (along with the message), to make the user happy by letting them see the invalid employee number. The next section gives you the solution for the above example.