When debugging Oracle PL/SQL applications, understanding the call stack is essential — especially when dealing with deeply nested procedures or unexpected behavior. While DBMS_UTILITY.FORMAT_CALL_STACK has long been used for this purpose, it’s limited to ~2000 bytes and often truncates valuable information.

In this article, I’ll show you how to use Oracle’s UTL_CALL_STACK package to log the full call stack automatically using a trigger and an autonomous transaction — a technique that’s especially useful in enterprise environments.

Why UTL_CALL_STACK?
 

Oracle 12c introduced UTL_CALL_STACK, which provides structured access to:

• Full call stack without limits
• Lexical and dynamic depth
• Line numbers
• Fully qualified subprogram names

This allows for precise and complete tracing of execution paths — perfect for logging and diagnostics.

🧱 Step 1: Create a Logging Table

We’ll store the call stack in a dedicated table:

CREATE TABLE call_stack_log (
  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  call_stack CLOB
);

🧠 Step 2: Create a Procedure with Autonomous Transaction

This procedure builds the call stack and logs it independently of the main transaction:

CREATE OR REPLACE PROCEDURE log_call_stack AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  depth PLS_INTEGER := UTL_CALL_STACK.dynamic_depth;
BEGIN
  FOR i IN REVERSE 1 .. depth LOOP
    result := result || 
      'Lexical Depth: ' || UTL_CALL_STACK.lexical_depth(i) ||
      ', Dynamic Depth: ' || i ||
      ', Line: ' || TO_CHAR(UTL_CALL_STACK.unit_line(i)) ||
      ', Subprogram: ' || UTL_CALL_STACK.concatenate_subprogram(UTL_CALL_STACK.subprogram(i)) || CHR(10);
  END LOOP;

  INSERT INTO call_stack_log (call_stack) VALUES (result);
  COMMIT;
END;
/

 PRAGMA AUTONOMOUS_TRANSACTION ensures the log is committed even if the main transaction fails.

Step 3: Add a Trigger to Log on Insert

Attach this to any table where you want to trace inserts:

CREATE OR REPLACE TRIGGER trigger_log_call_stack
AFTER INSERT ON <your_table>
FOR EACH ROW
BEGIN
  -- Call the procedure created.
  log_call_stack;
END;
/

**Replace <your_table> with the actual table name of yours.

Benefits

• Structured output for easier analysis.
• Full visibility into execution paths.
• Safe logging via autonomous transactions.

*Please feel free to add your thoughts and correct if anything is incorrect.