=================================================
Website      : http://www.arshltd.com
Author       : Arsh Ltd
Category     : Database Administration
Title        : PLSQL Exception Handling
Description  : Discusses various ways to handle exceptions in PL/SQL
=================================================

Background: When you are writing some PL/SQL code i.e. procedures, functions or packages, you most certainly would come across situations where you need to handle exceptions that might happen when your code executes. Exceptions get raised since your data may not be as you’d expect it to be, hence your code need to be robust enough to handle any eventuality.

1. Built in exceptions
2. User defined expceptions

Built in exceptions are pre-defined in Oracle and get raised automatically when such an event happens. Say for example, if you are selecting data from a table on certain condition, and no records are returned satisfying that condition, then the built in exception “NO_DATA_FOUND” is raised automatically by your code. So, while coding, you can define what you want to do if that exception is raised.

User defined exceptions are the ones which you define in your code. They could be implementation of your business logic.

Please note that it is a good programming practice to code exceptions so that your code handles them gracefully rather than your PL/SQL terminating abruptly.

A small example to demonstrate how to code built in exception -

Create or replace procedure p_excep_test is
  Myvar1    number := 10;
  Myvar2    number := 0;
  Myvar3    number;
Begin
  Myvar3 := Myvar1/Myvar2; --This will raise ZERO_DIVIDE automatically
  -----
  -----
  Raise zero_divide; --This will raise ZERO_DIVIDE manually
Exception
  When ZERO_DIVIDE then
    --Write code that you want executed when 
    --zero_divide exception is raised
    Dbms_output.put_line (sqlcode);
    Dbms_output.put_line (sqlerrm);
End;

A small example to demonstrate how to code user defined exception -

You need to declare a user defined exception before you can raise it (see in the declaration area). Then in your code when you come across an area where you think will be an exception situation (based on your business logic), you can raise this user defined exception which will take control of the flow into Exception area at the bottom where that exception has been coded.

Create or replace procedure p_excep_test is
  Myexcep   exception;
Begin
  -----
  -----
  Raise myexcep; --This is where user defined exception is raised
  -----
  -----
Exception
  When Myexcep then
    --Write code that you want executed when Myexcep exception is raised
    Dbms_output.put_line (sqlcode);
    Dbms_output.put_line (sqlerrm);
  When Others then
    -----
End;

We also have a “Catch All” built in exception (as demonstrated in previous procedure). This exception is automatically raised if there is any error in the code (i.e. exception situation) and that specific exception has not been coded. Hence “Catch All”. Please note that this is NOT a good programming practice to use “When Others” exception, as your programming/data issues will get unnoticed.

Finally, there is another way in which you can raise errors in your PL/SQL code rather than the code terminating abruptly as a result of exception. This method is a nice way of implementing your business or user defined meaningful error messages rather than default message which comes up. This is done by using RAISE_APPLICATION_ERROR. Following piece of code demonstrates just that –

Declare
Begin
  -------------
  -------------  

  If (some error condition) then
    raise_application_error( -20001, 'An issue occurred due to .....' );
  end if;
End;

And if that condition happens, you will see the following -

*
ERROR at line 1:
ORA-20001: An issue occurred due to .....

Also it is important to note that you might want to write some sequence of code and then come out of the procedure in a graceful way, then in that case do what you want and right at the end put a raise_applciation_error();