Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks

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.

Declare
  v_empno emp.empno%type:=&empno;
  v_inc emp.sal%type:=&increment;
  v_sal emp.sal%type;
  e_High_Sal exception;
Begin
  Select sal into v_sal
  From emp Where empno=v_empno;

  If v_sal>2000 then
    Raise e_High_Sal;
  End if;

  Update emp set sal=sal+ v_inc
  Where empno=v_empno;

  dbms_output.put_line(‘Successfully Updated’);

  Exception
  When e_High_Sal then
    dbms_output.put_line(‘Salary is Already High’);
End;

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.

 

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.

{mospagebreak title=The solution}

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

{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.

Declare
  v_empno1 emp.empno%Type := &empno1;
  v_ename emp.ename%Type;
  v_empno2 emp.empno%Type := &empno2;
Begin
  Begin
    Select ename into v_ename
    From emp Where empno=v_empno1;
    dbms_output.put_line(‘Name: ‘||v_ename);
  Exception
    When no_data_found then
      dbms_output.put_line(‘Employee not found with ‘||v_empno1);
  End;

 

  Begin
    Select ename into v_ename
    From emp Where empno=v_empno2;
    dbms_output.put_line(‘Name:’||v_ename);
  Exception
    When 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_empno1 emp.empno%Type := &empno1;
  v_empno2 emp.empno%Type := &empno2;
Begin

 

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

 

 

  Declare
    v_ename2 emp.ename%Type;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line(‘Name:’||v_ename2);
  Exception
    When 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:

Begin

 

  Declare
    v_ename1 emp.ename%Type;
    v_empno1 emp.empno%Type := &empno1;
  Begin
    Select ename into v_ename1
    From emp Where empno=v_empno1;
    dbms_output.put_line(‘Name: ‘||v_ename1);
  Exception
    When no_data_found then
      dbms_output.put_line(‘Employee not found with ‘||v_empno1);
  End;

 

 

  Declare
    v_ename2 emp.ename%Type;
    v_empno2 emp.empno%Type := &empno2;
  Begin
    Select ename into v_ename2
    From emp Where empno=v_empno2;
    dbms_output.put_line(‘Name:’||v_ename2);
  Exception
    When no_data_found then
      dbms_output.put_line(‘Employee not found with ‘||v_empno2);
  End;

End;

That is how we can separate every aspect of the program within a PL/SQL block into several blocks. And each of these blocks can be further separated or nested any number of times.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort