HomeOracle Page 2 - Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks
A frequently faced problem in exception handling - 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.
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.