Oracle UTL_CALL_STACK
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides an interface to provide information about currently executing subprograms. Functions return subprogram names, unit names, owner names, edition names, and line numbers for given dynamic depths. Other functions return error stack information.
AUTHID DEFINER
Data Types TYPE UNIT_QUALIFIED_NAME IS VARRAY(256) OF VARCHAR2(32767);
Dependencies
PLITBLM UTL_TCP  
Documented Yes
Exceptions
Error Code Reason
64610 BAD_DEPTH_INDICATOR: This exception is raised when a provided depth is out of bounds. Dynamic and lexical depth are positive integer values. Error and backtrace depths are non-negative integer values and are zero only in the absence of an exception.
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/utlcstk.sql
Subprograms
 
BACKTRACE_DEPTH
Returns the number of backtrace items in the backtrace: Zero if no exception utl_call_stack.backtrace_depth RETURN PLS_INTEGER;
See ERROR_MSG Demo Below
 
BACKTRACE_LINE
Returns the line number of the unit at the specified backtrace depth utl_call_stack.backtrace_line(backtrace_depth IN PLS_INTEGER) RETURN PLS_INTEGER;
See ERROR_MSG Demo Below
 
BACKTRACE_UNIT
Returns the name of the unit at the specified backtrace depth utl_call_stack.backtrace_unit(backtrace_depth IN PLS_INTEGER) RETURN VARCHAR2;
See ERROR_MSG Demo Below
 
CONCATENATE_SUBPROGRAM
Returns a concatenated form of a unit-qualified name utl_call_stack.concatenate_subprogram(qualified_name IN UNIT_QUALIFIED_NAME) RETURN VARCHAR2;
See SUBPROGRAM Demo Below
 
CURRENT_EDITION
Returns the current edition name of the unit of the subprogram at the specified dynamic depth utl_call_stack.current_edition(dynamic_depth IN PLS_INTEGER) RETURN VARCHAR2;
CREATE OR REPLACE PROCEDURE utc_current_edition AUTHID CURRENT_USER IS
BEGIN
  dbms_output.put_line(utl_call_stack.current_edition(2));
END utc_current_edition;
/

exec utc_current_edition;

-- this function returned no values in Beta and does not do so now ... SR opened with Oracle Support which confirmed the issue and opened bug 17061888
 
DYNAMIC_DEPTH
Returns the number of subprograms on the call stack utl_call_stack.dynamic_depth RETURN PLS_INTEGER;
See SUBPROGRAM Demo Below
 
ERROR_DEPTH
Returns the number of errors on the error stack utl_call_stack.error_depth RETURN PLS_INTEGER;
See SUBPROGRAM Demo Below
 
ERROR_MSG
Returns the error message of the error at the specified error depth utl_call_stack.error_msg(error_depth IN PLS_INTEGER) RETURN VARCHAR2;
CREATE OR REPLACE PROCEDURE utc_error_msg AUTHID CURRENT_USER IS
 i INTEGER;
BEGIN
  i := 1/0;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Backtrace Depth: ' || TO_CHAR(utl_call_stack.backtrace_depth));
    dbms_output.put_line('Backtrace Line: ' || TO_CHAR(utl_call_stack.backtrace_line(utl_call_stack.backtrace_depth)));
    dbms_output.put_line('Backtrace Unit: ' || utl_call_stack.backtrace_unit(utl_call_stack.backtrace_depth));
    dbms_output.put_line('Lexical Depth: ' || TO_CHAR(utl_call_stack.lexical_depth(utl_call_stack.error_depth)));
    dbms_output.put_line('Error Depth: ' || TO_CHAR(utl_call_stack.error_depth));
    dbms_output.put_line('Error Number: ' || utl_call_stack.error_number(utl_call_stack.error_depth));
    dbms_output.put_line('Error Message: ' || utl_call_stack.error_msg(utl_call_stack.error_depth));
END utc_error_msg;
/

exec utc_error_msg;
 
ERROR_NUMBER
Returns the error number of the error at the specified error depth utl_call_stack.error_number(error_depth IN PLS_INTEGER) RETURN PLS_INTEGER;
See SUBPROGRAM Demo Above
 
LEXICAL_DEPTH
Returns the lexical nesting level of the subprogram at the specified dynamic depth utl_call_stack.lexical_depth(dynamic_depth IN PLS_INTEGER) RETURN PLS_INTEGER;
See SUBPROGRAM Demo Above
 
OWNER
Returns the owner name of the unit of the subprogram at the specified dynamic depth utl_call_stack.owner(dynamic_depth IN PLS_INTEGER) RETURN VARCHAR2;
CREATE OR REPLACE PROCEDURE utc_owner AUTHID CURRENT_USER IS
BEGIN
  dbms_output.put_line(utl_call_stack.owner(1));
END utc_owner;
/

exec utc_owner;
 
SUBPROGRAM
Returns the unit-qualified name of the subprogram at the specified dynamic depth utl_call_stack.subprogram(dynamic_depth IN PLS_INTEGER) RETURN UNIT_QUALIFIED_NAME;
CREATE OR REPLACE FUNCTION utc_testfunc RETURN BOOLEAN AUTHID DEFINER IS
 uqn utl_call_stack.unit_qualified_name;
BEGIN
  uqn := utl_call_stack.subprogram(utl_call_stack.dynamic_depth);
  dbms_output.put_line('2nd Level: ' || TO_CHAR(uqn.COUNT));
  dbms_output.put_line(uqn(1));
  dbms_output.put_line('Concat SubProg: ' || utl_call_stack.concatenate_subprogram(uqn));
  dbms_output.put_line('Line Number 2: ' || utl_call_stack.unit_line(utl_call_stack.dynamic_depth));
  RETURN TRUE;
END utc_testfunc;
/

CREATE OR REPLACE PROCEDURE utc_subprogram AUTHID CURRENT_USER IS
 uqn utl_call_stack.unit_qualified_name;
 b   BOOLEAN;
BEGIN
  uqn := utl_call_stack.subprogram(utl_call_stack.dynamic_depth);
  dbms_output.put_line('Top Level: ' || TO_CHAR(uqn.COUNT));

  b := utc_testfunc;
  dbms_output.put_line(uqn(1));
  dbms_output.put_line('Line Number 1: ' || utl_call_stack.unit_line(utl_call_stack.dynamic_depth));
END utc_subprogram;
/

exec utc_subprogram;

CREATE OR REPLACE PACKAGE utc_pkg AUTHID CURRENT_USER IS
 uqn utl_call_stack.unit_qualified_name;
 PROCEDURE top_proc;
 PROCEDURE down_level;
END utc_pkg;
/

CREATE OR REPLACE PACKAGE BODY utc_pkg IS
  PROCEDURE top_proc IS
  BEGIN
--    utc_subprogram;
    utc_pkg.down_level;
  END top_proc;
  PROCEDURE down_level IS
  BEGIN
    ???
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Backtrace Unit: ' || utl_call_stack.backtrace_unit(0));
  END down_level;
END utc_pkg;
/

exec utc_pkg.top_proc;
exec utc_pkg.down_level;
 
UNIT_LINE
Returns the line number of the unit of the subprogram at the specified dynamic depth utl_call_stack.unit_line(dynamic_depth IN PLS_INTEGER) RETURN PLS_INTEGER;
See SUBPROGRAM Demo Above
 
Demo Working Area
Sandbox for code testing CREATE TABLE util_event_log (
run_no           NUMBER,        -- parameter
host_name        VARCHAR2(64),  -- sys_context
instance_id      NUMBER(2),     -- sys_context
instance_name    VARCHAR2(16),  -- sys_context
cdb_name         VARCHAR2(9),   -- sys_context
pdb_name         VARCHAR2(30),  -- sys_context
active_instances NUMBER(3),     -- dbms_utility
service_name     VARCHAR2(30),  -- sys_context
schema_name      VARCHAR2(30),  -- owa_util
edition_name     VARCHAR2(30),  -- sys_context
package_name     VARCHAR2(30),  -- owa_util
line_number      NUMBER(5),     -- owa_util
object_type      VARCHAR2(19),  -- owa_util


subprogram_owner VARCHAR2(30),
subprogram_name  VARCHAR2(30),

line_number      NUMBER,
beg_date         TIMESTAMP(6),
end_date         TIMESTAMP(6),


module_name      VARCHAR2(48),
action_name      VARCHAR2(32),
client_info      VARCHAR2(64),
severity         NUMBER(2),
sql_errno        NUMBER(5),
event_text       VARCHAR2(256),
log_comment      VARCHAR2(256))
PARTITION BY RANGE (instance_id)
INTERVAL (1) (
PARTITION root_par VALUES LESS THAN (2))
PCTUSED 99 PCTFREE 0;

PROCEDURE Log_Event_Start(
 pRunNo      IN util_event_log.run_no%TYPE,
 pBegDate    IN util_event_log.beg_date%TYPE DEFAULT SYSDATE,
 pSeverity   IN util_event_log.severity%TYPE DEFAULT NULL,
 pLogComment IN util_event_log.log_comment%TYPE)
IS
 -- run number is an IN param
 cHostName   CONSTANT util_event_log.host_name%TYPE     := sys_context('USERENV', 'SERVER_HOST');
 cInstID     CONSTANT util_event_log.instance_id%TYPE   := sys_context('USERENV', 'INSTANCE');
 cInstName   CONSTANT util_event_log.instance_name%TYPE := sys_context('USERENV', 'INSTANCE_NAME');
 cCDBName    CONSTANT sys_context('USERENV', 'DB_NAME')
 cContainer  CONSTANT sys_context('USERENV', 'CON_NAME');
 -- active_instances
 cServName   CONSTANT util_event_log.service_name%TYPE  := sys_context('USERENV', 'SERVICE_NAME');

 -- schema_name
 cEdition    CONSTANT utl_call_stack.current.edition;
 -- pkg name
 -- subprogram_name
 -- object_type


 cModName    CONSTANT util_event_log.module_name%TYPE   := sys_context('USERENV', 'MODULE');
 cActName    CONSTANT util_event_log.action_name%TYPE   := sys_context('USERENV', 'ACTION');
 cCliInfo    CONSTANT util_event_log.client_info%TYPE   := sys_context('USERENV', 'CLIENT_INFO');
 cTraceDepth CONSTANT utl_call_stack.backtrace_depth;

 vInstCount util_event_log.active_instances%TYPE;
 vInstTab dbms_utility.instance_table;
 vLineNumber all_source.line%TYPE;
 vObjType all_objects.object_type%TYPE;
 vPkgName all_objects.object_name%TYPE;
 vSchemaName all_objects.owner%TYPE;
 cpu_phys   NUMBER;
 plan_count NUMBER;


 PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  owa_util.who_called_me(vSchemaName, vPkgName, vLineNumber, vObjType);

  IF dbms_utility.is_cluster_database THEN
    dbms_utility.active_instances(vInstTab, vInstCount);
  END IF;


-- this recovers the physical CPUs and the resource mgr plan count
  dbms_wlm.get_cpu_count(cpu_phys, plan_count);


  FOR i IN 1 .. cTraceDepth LOOP
    INSERT /* mlib_utils.change_config05 */ INTO util_event_log
    (instance_id, run_no, schema_name, package_name, object_type, line_number,
    beg_date, host_name, instance_name, active_instances, service_name,
    module_name, action_name, client_info, severity, log_comment)
    VALUES
    (cInstID, pRunNo, vSchemaName, vPkgName, vObjType, vLineNumber,
    pBegDate, cHostName, cInstName, vInstCount, cServName,
    cModName, cActName, cCliInfo, pSeverity, pLogComment);
  END LOOP;
  COMMIT;
  -- this procedure intentionally does not contain exception handling: do not add one.
END Log_Event_Start;
/

Related Topics
DBMS_UTILITY
ORADEBUG
OWA_UTIL
Packages
PLSCOPE

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved