Obsessed with Oracle PL/SQL

The DBMS_UTILITY has long (since 10.2) offered three functions that are very handy when either tracing execution or logging errors: FORMAT_CALL_STACK - answering the question "How did I get here?" FORMAT_ERROR_STACK - answering the question "What was the error?" (or a stack of errors, depending on the situation) FORMAT_ERROR_BACKTRACE - answering the question "On what line was my error raised?" Therefore (and prior to 12.2), if you wanted to get the error information + the line number on which the error was raised, you would need to call both of the "*ERROR*" as in: CREATE OR REPLACE PROCEDURE p3 AUTHID DEFINER IS BEGIN p2; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack); DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace); RAISE; END; Of course, in the real world, you would not display the text on the screen. You would write them to a log table via an autonomous tra...

The DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (and similar functionality in the UTL_CALL_STACK package) is a tremendously helpful function. It returns a formatted string that allows you to easily trace back to the line number on which an exception was raised. You know what else is really helpful? The automatic optimization performed by the PL/SQL compiler. The default level is 2, which does an awful lot of optimizing for you. But if you want to get the most out of the optimizer, you can ratchet it up to level 3, which then added subprogram inlining . Unfortunately, these two wonderful features don't mix all that well. Specifically, if you optimize at level 3, then the backtrace may not point all the way back to the line number in your "original" source code (without inlining, of course). Run this LiveSQL script to see the following code below "in action." ALTER SESSION SET plsql_optimize_level = 2 / CREATE OR REPLACE PROCEDURE proc1 IS l_level INTEGER; ...

Just received this feedback on an Oracle Magazine article : I’ve just started using DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and it’s a great utility when the code contains no comments, but unless I’m missing something it’s not terribly useful with regard to pinpointing the exact line number when the code contains comments. Now, I must confess that I am tempted to respond with such questions along these lines: Did you try it out? That's the best way to learn! But I am always looking for another reason to post on my blog and add to the LiveSQL repository. So that's what I am doing! :-) The answer is quite straightforward: Comments do not interfere with "pinpointing the exact line number" on which the error was raised. But I bet you want proof, so let's get down to business. I create a procedure with comments and a RAISE statement: CREATE OR REPLACE PROCEDURE comments_throw_off_line# IS /* Here's my header Blah blah blah */ BEGIN DBMS_OU...