Tuesday, 13 February 2007

Where Did My PL/SQL Error?

When your PL/SQL block errors, it has always been a rudimentary task in identifying the error generated using a combination of SQLCODE and SQLERRM.

These 2 functions however do not tell us the exact line of code that propagated the error. This feature would be very handy when debugging code.

In Oracle 10g, there is a function called: DBMS_UTILITY.format_error_backtrace

This will return the line number that generated the error. It does not tell us what the SQL Error message is so I use it in conjunction with SQLERRM to quickly debug problematic code.

In a recent application, I used it as part of my error logging and reporting function that allows the support team to quickly troubleshoot errors. The more information about the error we can give them, the quicker they should be able to response. At least that’s the theory.

For a more detailed explanation of how to best utilise this function, have a look at this atricle