Error handling in pl/sql

Question

Hi, I'm Dinesh from Bhiwani, and I want to know about error handling in PL/SQL. Can anyone help me to find brief description on this topic?

Answer 1

Every block can have an exception handler. Example:

DECLARE
    /* declare your variables */
BEGIN
    /*Here is your code */
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        /* HAndle an error that gets raised when a query returns nothing */
    WHEN TOO_MANY_ROWS THEN
        /* HAndle the situation when too much data is returned such as with a select-into */
    WHEN OTHERS THEN
        /* Handle everything else*/
END;

This link will tell you more: http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10807/07_errs.htm

That link will show you more detail than I did, as well as examples on how to create your own exception names.

One item that always trips me up is that if you have a function and you fail to return a value in the exception handler, an exception gets thrown in the calling function. Not a big deal but I always seem to forget that one.

Answer 2

The Oracle article referenced in the other answer is well worth reading.

A couple of extra things to throw in - catching a PL/SQL exception loses the error stack - i.e. the information about exactly which line raised the exception.

This can make it difficult to debug blocks of code that contain multiple places that could raise the same exception (i.e. if you have more than one SQL statement that could return NO_DATA_FOUND). One option here is to log the full error stack as part of your exception handler.

EXCEPTION
    WHEN TOO_MANY_ROWS THEN
         myLogger('Some useful information',DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

If you do need to catch exceptions, keep your exception handling as local as possible to the point you want to catch, and only use WHEN OTHERS in the last resort.

You can also 'do something and re-raise the same exception'

EXCEPTION
     WHEN TOO_MANY_ROWS THEN
         closeSmtpConnection;
         RAISE;
END;

One of the most useful features is the ability to name and catch Oracle SQL internal exceptions.

DECLARE
   recompile_failed     EXCEPTION;
   PRAGMA EXCEPTION_INIT (recompile_failed,-24344);
BEGIN
   . . . . . .
EXCEPTION
   WHEN recompile_failed THEN 
      emailErrors(pObjectType,pObjectName);
END;

The flipside to this is the ability to raise user defined 'SQL' exceptions

RAISE_APPLICATION_ERROR(-20001,'my text')

This is the only way to propagate user defined text to a calling application, as user-defined pl/sql exceptions do not cross the 'scope' boundary.

Unfortunately, despite the documentation saying that the range -20000 to -20999 is available for user-defined exceptions, some of the Oracle extension packages use these serials, so you cannot depend on serial alone to identify an error in the calling language.

(Most people tend to wrap RAISE_APPLICATION_ERROR in other code to also log the error, and often to derive the error text from a table)

One trick I've found useful is to create a package with 'stateful' variables in the package body, and simple setter and getter functions. Unlike database updates, information in packages is NOT rolled back on error.

At the point of error, set information in your package, then retrieve it using getters in your calling language, to construct a 'native' exception.

As for user-defined pl/sql exceptions - these can be useful in local code, but in many cases they can be avoided by using a different control structure (i.e. avoid using them as an alternative GOTO).

Creating global exceptions on package headers, to specify the possible exceptions a package may return seems like a good idea, but the end result is that your calling code ends up with having to handle every potential exception that could be cast in any of the underlying packages.

Having gone down this route myself in the past, I would now recommend against it - make packages self-contained and either use RAISE_APPLICATION_ERROR or pass back errors as text.