monline

Under some circumstances a call to dbms_utility.format_call_stack() might return an empty string (NULL).

Try this:

1. Create a table to store some log info.
   Eg. CREATE TABLE my_log(log_date DATE NOT NULL, log_text VARCHAR2(2000));
2. Create a procedure that stores the result of dbms_utility.format_call_stack() in the log table.
   Eg.
   
   CREATE OR REPLACE PROCEDURE log_stack IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  sCallstack my_log.log_text%TYPE;
BEGIN
  sCallstack := SUBSTRB(dbms_utility.format_call_stack(), 1, 2000);
  INSERT INTO my_log(log_date, log_text) VALUES (SYSDATE, sCallstack);
  COMMIT;
END log_stack;
3. Create a function inside a package that calls the logger procedure in the package body initialization block.
   Eg.
   
   CREATE OR REPLACE PACKAGE pkg_test IS

FUNCTION test RETURN VARCHAR2;

END pkg_test;
/

CREATE OR REPLACE PACKAGE BODY pkg_test IS

FUNCTION test RETURN VARCHAR2 IS
BEGIN
  RETURN NULL;
END test;

BEGIN
  log_stack();
END pkg_test;
/
4. Call this function in the following query twice in the same session (eg. in SQLPLUS):
   
   SELECT
  pkg_test.test()
FROM
  dual

For me on Oracle 9iR2 (9.2.0.1.0 + x86 Windows, just to be precise ) the first run resulted in a normal call stack in the my_log table (most of the time), but all subsequent executions of the query resulted in an empty call stack. It seems dbms_utility.format_call_stack() has some flaws in it.