FORMAT_ERROR_BACKTRACE function - return a description of the call stack at time of most recent error

Authorization

Each line of the result describes an active routine on the call stack at the time of the error; the most recently invoked routine appears as the first line, followed by less recently invoked routines. In other words, the result describes the call chain at the time of the error, beginning with the most deeply nested routine, followed by its caller, the caller’s caller, and so on.

If no compiled SQL routine has encountered an error during the current session, 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 table T1(C1 integer, C2 integer, tag varchar(32)) @ 
insert into T1 values (1, 6, 'VI'), (2, 10, 'X'), (3, 11, 'XI'), (4, 48, 'XLVIII') @ 

create or replace procedure B( 
in colname varchar(128), 
in value integer, 
in tag varchar(32)) 
language SQL 
begin 
declare stmt_text varchar(256); 
declare S1 statement; 

set stmt_text = 'update T1 set tag = ? where ' || colname || ' = ?'; 
prepare S1 from stmt_text; 
execute S1 using tag, value; 
end @ 

create or replace procedure A() 
begin 
call B('C1', 1, 'six'); 
call B('C2', 11, 'eleven'); 
call B('C3', 0, 'zero'); -- will produce an error 
call B('C2', 48, 'forty-eight'); 
end @ 

begin 
call A; 
end @ 
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: 
SQL0206N "C3" is not valid in the context where it is used. SQLSTATE=42703 

values dbms_utility.format_error_backtrace() 

1 
-----------------------------------------------------------------------//-- 
11 procedure MYSCHEMA.B (specific SQL150217153622825); SQLCODE=-206 
5 procedure MYSCHEMA.A (specific SQL150217153623026) 
2 anonymous block (specific SQL150217153624330) 

1 record(s) selected.