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.
- WHEN OTHERS
- Using the predefined VALUE_ERROR exception (not INVALID_NUMBER, which is the error that would be raised in SQL).
- 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.
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.