When PL/SQL code needs to know where it is running, or where an error came from, the classic DBMS_UTILITY functions return one formatted block of text that you have to parse. UTL_CALL_STACK returns the same information as separate values: the depth of the stack, and for each level the subprogram name, unit, and line number, including subprograms inside packages.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
-- the current call stack, 1 = the code that is running utl_call_stack.dynamic_depth utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(level)) utl_call_stack.unit_line(level) -- inside an exception handler utl_call_stack.error_depth / error_number(level) / error_msg(level) utl_call_stack.backtrace_depth / backtrace_unit(level) / backtrace_line(level)
Where Am I?
WHERE_AM_I prints every level of the stack. It is called from a private procedure inside a package, which is called from a public one.
Example:
create or replace procedure where_am_i is
begin
for i in 1 .. utl_call_stack.dynamic_depth loop
dbms_output.put_line(i || ': ' || utl_call_stack.concatenate_subprogram(
utl_call_stack.subprogram(i))
|| ' line ' || utl_call_stack.unit_line(i));
end loop;
end;
/
create or replace package trip_planner is
procedure plan;
end;
/
create or replace package body trip_planner is
procedure check_route is begin where_am_i; end;
procedure plan is begin check_route; end;
end;
/
exec trip_planner.planOutput:
Procedure WHERE_AM_I compiled Package TRIP_PLANNER compiled Package Body TRIP_PLANNER compiled 1: WHERE_AM_I line 4 2: TRIP_PLANNER.CHECK_ROUTE line 2 3: TRIP_PLANNER.PLAN line 3 4: __anonymous_block line 1 PL/SQL procedure successfully completed.
Level 1 is WHERE_AM_I itself, and the package levels show the subprogram names, TRIP_PLANNER.CHECK_ROUTE and TRIP_PLANNER.PLAN, which the formatted call stack text does not give for code inside packages. The example drops the package and the procedure afterward.
Errors and Backtraces
First, two procedures where LEVEL_TWO prints the formatted call stack and raises an error, with the DBMS_UTILITY text for comparison:
Example:
create or replace procedure level_two is
begin
dbms_output.put_line(rtrim(dbms_utility.format_call_stack, chr(10)));
raise_application_error(-20005, 'Fare table locked');
end;
/
create or replace procedure level_one is
begin
level_two;
end;
/
begin
level_one;
exception
when others then
dbms_output.put_line('Error stack: ' || dbms_utility.format_error_stack);
dbms_output.put_line('Backtrace: ' || dbms_utility.format_error_backtrace);
end;
/Output:
Procedure LEVEL_TWO compiled Procedure LEVEL_ONE compiled ----- PL/SQL Call Stack ----- object line object handle number name 0x81763428 3 procedure NIMBUS.LEVEL_TWO 0x9350d668 3 procedure NIMBUS.LEVEL_ONE 0x7cdae080 2 anonymous block Error stack: ORA-20005: Fare table locked ORA-06512: at "NIMBUS.LEVEL_TWO", line 4 ORA-06512: at "NIMBUS.LEVEL_ONE", line 3 Backtrace: ORA-06512: at "NIMBUS.LEVEL_TWO", line 4 ORA-06512: at "NIMBUS.LEVEL_ONE", line 3 ORA-06512: at line 2 PL/SQL procedure successfully completed.
The same error read level by level with UTL_CALL_STACK:
Example:
begin
level_one;
exception
when others then
for i in 1 .. utl_call_stack.error_depth loop
dbms_output.put_line('error ' || i || ': ORA-'
|| to_char(utl_call_stack.error_number(i), 'fm00000')
|| ': ' || utl_call_stack.error_msg(i));
end loop;
for i in 1 .. utl_call_stack.backtrace_depth loop
dbms_output.put_line('at ' || nvl(utl_call_stack.backtrace_unit(i), 'anonymous block')
|| ' line ' || utl_call_stack.backtrace_line(i));
end loop;
end;
/Output:
----- PL/SQL Call Stack ----- object line object handle number name 0x81763428 3 procedure NIMBUS.LEVEL_TWO 0x9350d668 3 procedure NIMBUS.LEVEL_ONE 0x77394060 2 anonymous block error 1: ORA-20005: Fare table locked error 2: ORA-06512: at "NIMBUS.LEVEL_TWO", line 4 error 3: ORA-06512: at "NIMBUS.LEVEL_ONE", line 3 at NIMBUS.LEVEL_TWO line 4 at NIMBUS.LEVEL_ONE line 3 at anonymous block line 2 PL/SQL procedure successfully completed.
ERROR_NUMBER and ERROR_MSG give each error on the stack separately, and BACKTRACE_UNIT and BACKTRACE_LINE give the line where it was raised, ready to log in columns instead of one string. The call stack lines at the top come from LEVEL_TWO, which prints them before raising. The example drops both procedures afterward.
Things to Know
- ERROR_ and BACKTRACE_ functions only have values while an exception is being handled.
- BACKTRACE_UNIT is NULL for an anonymous block, so supply your own label as the example does.
- Level 1 of DYNAMIC_DEPTH is the subprogram calling UTL_CALL_STACK; put the code in a logging procedure and start from level 2 to report its caller.
Related Guides
- Error Handling with Dbms_utility.format_error_backtrace
- How to Use SQLCODE in PL/SQL Exception Handlers
Conclusion
UTL_CALL_STACK gives the call stack, the error stack, and the backtrace as separate values with subprogram names inside packages. Use it to log exactly where code ran and where an error was raised, instead of parsing formatted text.
