Votes

Show package's subprogram name in call stack and backtrace (details)
 
Dear Bryn,

I love PL/SQL and in particular makes lots of use of both DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE in my error management and tracing.

It sure would be wonderful, though, if I could see the actual names of subprograms in those stacks...

Thanks so much!

The DBMS_UTILITY.FORMAT_CALL_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE functions return incredibly useful information, namely and respectively:

* the execution call stack: that is, the sequence of program calls (including line numbers) that got you to the current place in your code.

* the trace of program calls and line numbers back to the line on which the last error was raise (added in Oracle Database 10g Release 2).

Unfortunately, if the program in the stack is defined in a package (very likely), Oracle only shows you the package name, not the name of the actual subprogram. This means that you cannot automatically determine the name of the procedure or function that was called.


Add the name of the subprogram to any of the stacks that Oracle produces. 


Here's an example of a call stack that shows owner name and package name, but no subprograms:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2A8BF0E0      1130  package body QCTO1600_NEW.QU_RUNTIME
2A8BF0E0      1160  package body QCTO1600_NEW.QU_RUNTIME
272AFF80       491  package body QCTO1600_NEW.QU_RESULT_CP
272F9E04       567  package body QCTO1600_NEW.QU_RESULT_XP
2714BB24      1125  package body QCTO1600_NEW.QU_TEST
2714BB24      1174  package body QCTO1600_NEW.QU_TEST
271B6650         4  anonymous block


Wouldn't this be so much better:

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2A8BF0E0      1130  package body QCTO1600_NEW.QU_RUNTIME.LOG_ERROR
2A8BF0E0      1160  package body QCTO1600_NEW.QU_RUNTIME.RAISE_ERROR
272AFF80       491  package body QCTO1600_NEW.QU_RESULT_CP.INSERT_ROWS
272F9E04       567  package body QCTO1600_NEW.QU_RESULT_XP.BULK_INSERT
2714BB24      1125  package body QCTO1600_NEW.QU_TEST.POPULATE_RESULTS
2714BB24      1174  package body QCTO1600_NEW.QU_TEST.RUN_TEST
271B6650         4  anonymous block


Steven Feuerstein, PL/SQL Evangelist, Quest Software: "Help me help Oracle improve the PL/SQL language!"


Bryn Llewellyn: "We love to hear from PL/SQL developers. Let us know what is important to you!"

Links

PL/SQL Obsession
Apex Evangelists
O'Reilly Books on Oracle
OTN PL/SQL Best Practices
OTN PL/SQL Page
Steven Feuerstein's Blog