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
DBMS_OUTPUT.put_LINE('I'm the first');
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.