Login

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.

Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you refer to the documentation for the version you are using, if any of the programs fail to execute.

User defined exceptions

The sixth part of my series (the previous article) completely focused on predefined exceptions in Oracle PL/SQL. Now, how do we create our own exception, raise it and handle it?

The declaration of a user defined exception is similar to that of a variable declaration. Instead of providing a data type to that variable, we provide a keyword EXCEPTION. PL/SQL runtime doesn’t know when to raise (or fire) our exception. So, it is our responsibility to inform the PL/SQL runtime when it has to raise (or fire). Once the exception is raised, it is handled in the same way as we handle predefined exceptions. Let us consider the following example.

From the above program, we can observe that ‘e_High_Sal’ is declared as an EXCEPTION. The exception gets raised when the salary is above two thousand. The ‘raise’ statement makes the flow of execution jump immediately to the exception section (skipping all the statements in between). If the salary is above two thousand, the exception ‘e_High_Sal’ gets handled through a ‘when’ statement within the exception section, and displays the message ‘Salary is Already High’.

You may be wondering why this much of a mechanism is necessary to display a simple message based on a condition. The same thing can be achieved very easily and comfortably by using an IF..ELSE construct. In the case of above example, I should admit it.

Another question you might as is, can’t we write any program in PL/SQL without using a user defined exception? To which the answer is almost YES. The user defined exceptions are not necessary (basically for simple programs). But when your code within the PL/SQL block grows more and more, or blocks get nested more and more, or for efficiently coding sub-programs or packages, you would definitely appreciate the use of user-defined exceptions.

{mospagebreak title=A frequently faced problem in exception handling}

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.

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.

{mospagebreak title=The solution}

The following program solves the problem explained in the previous section:

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

{mospagebreak title=The most efficient solution}

This section not only introduces you the best solution to the problems defined in the previous sections, but also introduces you to nested blocks in the PL/SQL programming structure. Before directly looking into the program, let me define for you the concept of nested blocks.

Everybody knows that a typical PL/SQL block structure starts with DECLARE (the declaration section is actually optional), continues through BEGIN, handles exceptions through the EXCEPTION section(which is also optional), and finally ends at END. This PL/SQL block can be further nested inside with its own nested PL/SQL blocks (again with DECLARE, BEGIN, EXCEPTION and END sections). And they can be nested any number of times.

If properly nested and defined, each nested block will have its own boundary of variable declarations or exceptions, which will not be seen (or accessed) by other parallel nested blocks. The following example introduces you to two parallel nested blocks inside a main PL/SQL block. The example also gives you the best solution to solve the problems explained in the previous two sections.

BeginSelect ename into v_ename From emp Where empno=v_empno2;
dbms_output.put_line(‘Name:’||v_ename); ExceptionWhen no_data_found then
dbms_output.put_line(‘Employee not found with ‘||v_empno2); End; End;

In the above program, every SELECT gets executed within its own block (having no relation to one another) and displays either employee information or an exception message. We also need to observe that the two nested blocks are using variables from the main (parent) block without having their own local declarations. And finally, one should agree that the above style of programming looks clearer and easier to read.

{mospagebreak title=Nested blocks with local variable declarations}

This is just an extension to the previous section. I will show you the sample example in the previous section, with a few variations in nested blocks. The only issue I wanted to address here is that the nested blocks can have their own local declarations without having any relation to their parent or other friendly (parallel) blocks.

Declare
v_ename1 emp.ename%Type; BeginSelect ename into v_ename1 From emp Where empno=v_empno1;
dbms_output.put_line(‘Name: ‘||v_ename1); ExceptionWhen no_data_found then
dbms_output.put_line(‘Employee not found with ‘||v_empno1); End;

Declare
v_ename2 emp.ename%Type; BeginSelect ename into v_ename2 From emp Where empno=v_empno2;
dbms_output.put_line(‘Name:’||v_ename2); ExceptionWhen no_data_found then
dbms_output.put_line(‘Employee not found with ‘||v_empno2); End;

End;

From the above program, you can clearly observe that every nested block has its own DECLARE section. The variable ‘v_ename1′ in the first nested block cannot be accessed beyond the first block (in this case, cannot be accessed by the parent block or even the second block) and the case of ‘v_ename2′ is similar. You can also localize all variables (based on the situation) and remove the declarations section from the parent block, as shown in the following example: