Back to the Basics: Exception Handling in PL/SQL

In our previous blog posts, we had covered the scenarios of using multiple exceptions and using NO_DATA_FOUND exception when used with bulk collection. In this blog post, we will cover the basic exception handling scenarios.

Exceptions are constructs used in PL/SQL programming to handle error conditions. Oracle provides some pre-defined exceptions. These exceptions are very specific (to prevent duplicate record in the table) to more general (to handle any errors). Users can also define his or her own exceptions. When dealing with exceptions, we have to handle the specific exceptions first before handling the general exceptions. Let us start with example. We will first create the table.

Now we will try to insert two records with the same TEST_ID. Oracle provides pre-defined exception WHEN DUP_VAL_ON_INDEX to handle the error due to inserting duplicate record. Oracle also provides WHEN OTHERS exception to handle any error. Let us execute following PL/SQL block and observe the results.

WHEN OTHERS THEN
*
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Since WHEN OTHERS is general exception, it should be last in the exception block if more than one exception are defined in the exception block. This makes sure that any specific exception is handled correctly and appropriate actions are taken once exception is caught. Now let us change the order and execute the PL/SQL block again.

Other important thing to note is to raise an error when exception is caught using WHEN OTHERS exception. When RAISE or RAISE_APPLICATION_ERROR is used, execution stops at that point and exception is thrown back to its caller program. If RAISE is not used, then it may hide the error without stopping the execution. Following is an example.

Above example is just for illustration. Even though we have caught the exception, execution completed successfully. This holds true even if error is logged into any other table. If there is no mechanism in place to examine the errors immediately after execution, user never know that error really occurred which may be very scary especially when errors occurred due to space limitation or any other database errors. This is why using RAISE is important for WHEN OTHERS exception.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on November 29, 2007 at 12:53 am and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.