PL/SQL Exception Handling Performance

Tom Kyte wrote about the evils of WHEN OTHERS as an exception handling mechanism, and I thought I’d run a quick test to see if there were performance implications of different methods.

I used a function to test whether a string represents a number that is convertable by To_Number() in PL/SQL, and used three methods of handling the error.

  1. WHEN OTHERS
  2. Using the predefined VALUE_ERROR exception (not INVALID_NUMBER, which is the error that would be raised in SQL).
  3. Using a user-associated named exception.

I ran each function 1,000,000 times for input values that were and were not convertable to numbers, and ran each test three times to check repeatability.

Test script

Script output

The script output shows that there was no appreciable difference between the three methods.  Hopefully that’s another nail in the coffin of WHEN OTHERS, if any more were needed.

Advertisements

4 thoughts on “PL/SQL Exception Handling Performance

  1. he following error has occurred:

    ORA-20101: Scheme Def not available 3–27-APR-05
    ORA-06512: at one table.how to rectify the problem

    regards,
    maya

  2. David,

    I thought the general gripe with using when others, was that it was just a generic exception that doesn’t really represent anything other than ‘oh no, something went wrong’ ?

    Al.

  3. Alistair,

    yes, that’s true — however one of the common places that WHEN OTHERS THEN is found is in this type of conversion function. In fact, I believe that he has used it in those circumstances on AskTom. I was merely checking that there was no performance advantage in WHEN OTHERS THEN.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s