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

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.