Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks (Page 1 of 5 )
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.
Next: A frequently faced problem in exception handling >>
More Oracle Articles
More By Jagadish Chatarji
/ 23 
