FORMAT_CALL_STACK function - return a description of the current call stack

Authorization

Each line of the result describes an active routine on the call stack. The most recently invoked routine appears as the first line, followed by less recently invoked routines. So, the result describes the current call chain, beginning with the most deeply nested routine, followed by its caller, the caller’s caller, and so on.

The invocation of the function itself is not included in the reported call stack.

If no other routines are active in the current session at the time FORMAT_CALL_STACK() function is invoked, the function returns NULL.

Each line of the call stack begins with the line number, relative to the first line of the routine, which is line 1. If line number information is unavailable for that routine, the line is displayed as a hyphen (-).

After the line number, the routine type is displayed as a procedure, function, trigger, or anonymous block (compound statement). External routines are also displayed. However, no distinction is made between external procedures or external functions. Line number information is unavailable for external routines.

The routine type is followed by the fully-qualified routine name: Routine schema, module name (if applicable), routine name, and then the subroutine name (if applicable). The specific name of the routine is reported after its fully-qualified name. The schema of the specific name is not reported, and is assumed to be the same as in the fully-qualified routine name.

EXECUTE privilege on the DBMS_UTILITY module.

Example

create global temporary table error_log(ts timestamp, message varchar(4096)) 
on commit preserve rows 
not logged on rollback preserve rows 
in error_ts % 

-- ... 

create procedure C(in N integer, in D integer) 
language SQL 
begin 
declare X double; 

declare continue handler for sqlexception 
begin 
declare message varchar(255); 
declare NL char(1) default x'0a' ; 

get diagnostics exception 1 message = message_text; 

insert into error_log values( 
current_timestamp, 
'N = ' || N || '; D = ' || D || NL || 
message || NL || NL || 
'Line Routine' || NL || 
'-------- ----------------' || NL || 
dbms_utility.format_call_stack()); 
end; 

set X = cast(N as double) / cast(D as double); 
end % 

select * from error_log order by ts asc 

TS MESSAGE 
-------------------------- ---------------------------------------------//-- 
2015-01-27-14.34.30.972622 N = 10; D = 0 
SQL0801N Division by zero was attempted. SQLSTATE=22012 

Line Routine 
-------- ---------------- 
13 procedure MYSCHEMA.C (specific SQL150217153125821) 
7 procedure MYSCHEMA.B (specific SQL150217153126322) 
4 procedure MYSCHEMA.A (specific SQL150217153126423) 

1 record(s) selected.