Confused by your error backtrace? Check the optimization level!

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'
/