Lack of Exception Handling Yet Again

What’s a good method of getting those deep layered exceptions to propagate for effective troubleshooting? My personal favorite is dbms_utility.format_error_backtrace . Even if the code doesn’t have exception handling I will at times add this to the code in order to effectively troubleshoot. Easier of course in non-production systems, but the same can be done in production. If someone of course, is monitoring recompiled code or creation of objects this might get flagged. I don’t recommend it for high executed code in production without of course going through your change control process. Could end up being nasty if you’re not careful.

Simply add dbms_output.put_line(dbms_utility.format_error_backtrace) to your exception handling:


CREATE OR REPLACE PROCEDURE third
IS
BEGIN
DBMS_OUTPUT.put_LINE('I'm the first');
first;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
DBMS_OUTPUT.put_line(dbms_utility.format_error_backtrace);
END;

I used both the FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE backtrace doesn’t include the error message. alternatively SQLERRM could be used as well. Depending on how deep into the call stack the error is being raised it may sever you will to substr the error message. Maybe you do only need the ORA error number and line number.

Remember if using multiple exceptions you will need to re-raise the exception to the outer most exception.

A developer can make friends with the DBA staff by including well-defined exception handling within their code. And if they don’t we can guide them to the light. When all else fails we can add in the needed lines for troubleshooting. I find this so much easier than trying to debug in production. Just because you can doesn’t mean that you should.

Advertisements

Tagged: ,

Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: