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;

PROCEDURE inline_proc1
IS
BEGIN
RAISE PROGRAM_ERROR;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('inline_proc1 handler');

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
BEGIN
SELECT plsql_optimize_level
INTO l_level
FROM user_plsql_object_settings
WHERE name = 'PROC1';

DBMS_OUTPUT.put_line ('Opt level = ' || l_level);

inline_proc1;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('inline handler');

DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
/

BEGIN
proc1;
END;
/

ALTER SESSION SET plsql_optimize_level = 3
/

ALTER PROCEDURE proc1
COMPILE
/

BEGIN
proc1;
END;
/

Opt level = 2
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 8

inline handler
ORA-06512: at "STEVEN.PROC1", line 15
ORA-06512: at "STEVEN.PROC1", line 8
ORA-06512: at "STEVEN.PROC1", line 25


Opt level = 3
inline_proc1 handler
ORA-06512: at "STEVEN.PROC1", line 25

inline handler
ORA-06512: at "STEVEN.PROC1", line 25
ORA-06512: at "STEVEN.PROC1", line 25

I hope to have an update from the PL/SQL dev team on this topic soon, but I wanted to make you aware of this in case you get all confused and frustrated.

Check your optimization level! Oh, how do you do that? Here you go:

SELECT p.plsql_optimize_level
FROM user_plsql_object_settings p
WHERE name = 'PROC1'
/