''How did I get here?'' DBMS_UTILITY.FORMAT_CALL_STACK

View All Scripts Login to Run Script

Script Name ''How did I get here?'' DBMS_UTILITY.FORMAT_CALL_STACK
Description Use the DBMS_UTILITY.FORMAT_CALL_STACK function to answer the question "How did I get here?". Note that this function only shows you the name of the program unit (e.g., package name) and not the name of the subprogram in that program unit. In 12.1 and higher, you can also use the UTL_CALL_STACK package, which offers the advantage of showing you the name of the subprogram from which the call stack was requested.
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday January 28, 2016

Statement 1

CREATE OR REPLACE PROCEDURE proc1 
IS 
BEGIN 
   DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack); 
END;

Procedure created.

Statement 2

CREATE OR REPLACE PACKAGE pkg1 
IS 
   PROCEDURE proc2; 
END pkg1;

Package created.

Statement 3

CREATE OR REPLACE PACKAGE BODY pkg1 
IS 
   PROCEDURE proc2 
   IS 
   BEGIN 
      proc1; 
   END; 
END pkg1;

Package Body created.

Statement 4

CREATE OR REPLACE PROCEDURE proc3 
IS 
BEGIN 
   FOR indx IN 1 .. 1000 
   LOOP 
      NULL; 
   END LOOP; 
 
   pkg1.proc2; 
END;

Procedure created.

Statement 5

CALL proc3

----- PL/SQL Call Stack -----
  object      line  frame       object
  handle    number  size        name
0x123c0a6b8         4          80  procedure SQL_IQHYXMBDHHTXVDBANGHFKHZZJ.PROC1
0x76f00db0         6          24  package body SQL_IQHYXMBDHHTXVDBANGHFKHZZJ.PKG1.PROC2
0x729769f8         9          24  procedure SQL_IQHYXMBDHHTXVDBANGHFKHZZJ.PROC3
0xfd314308         1          24  anonymous block
0x6de8d310      1721          88  package body SYS.DBMS_SQL.EXECUTE
0x105955868      1368        4896  package body LIVESQL.ORACLE_SQL_EXEC.RUN_BLOCK
0x105955868      1462         968  package body LIVESQL.ORACLE_SQL_EXEC.RUN_SQL
0x105955868      1792        4744  package body LIVESQL.ORACLE_SQL_EXEC.IMITATE_SQLPLUS_CMD
0x105955868      1894        5024  package body LIVESQL.ORACLE_SQL_EXEC.RUN_STATEMENTS
0x105955868      2019        2528  package body LIVESQL.ORACLE_SQL_EXEC.RUN_STMTS
0x61f6a410      2307        3712  package body LIVESQL.ORACLE_SQL_SCHEMA.RUN_SAVED_SESSION
0x12a176270       341         112  package body LIVESQL.ORACLE_SQL_SCHEMA_PUB.RUN_SAVED_SESSION
0x12a9cdfb0        22        1616  anonymous block
0x6de8d310      1721          88  package body SYS.DBMS_SQL.EXECUTE
0x72201a30      1879         936  package body APEX_050100.WWV_FLOW_DYNAMIC_EXEC.RUN_BLOCK5
0x72201a30       935         168  package body APEX_050100.WWV_FLOW_DYNAMIC_EXEC.EXECUTE_PLSQL_CODE
0x65ed0ed8        71         256  package body APEX_050100.WWV_FLOW_PROCESS_NATIVE.PLSQL
0x65ed0ed8      1132        4560  package body APEX_050100.WWV_FLOW_PROCESS_NATIVE.EXECUTE_PROCESS
0x12af037f8      2397        2784  package body APEX_050100.WWV_FLOW_PLUGIN.EXECUTE_PROCESS
0xfe1a44e8       191        2392  package body APEX_050100.WWV_FLOW_PROCESS.PERFORM_PROCESS
0xfe1a44e8       434       11752  package body APEX_050100.WWV_FLOW_PROCESS.PERFORM
0xfd3d8cc0      4851          40  package body APE

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database