Errors
In order to examine the format of
format_error_stack and
format_error_backtrace,
these PL/SQL packages were written.
When executed, dbms_utility.format_error_stack and dbms_utility.format_error_backtrace return a result like shown below
format_error_stack:
ORA-00900: invalid SQL statement
ORA-06512: at "RENE.TQ84_PKG_1", line 11
ORA-06512: at "RENE.TQ84_PKG_2", line 11
ORA-06512: at "RENE.TQ84_PKG_2", line 4
ORA-06512: at "RENE.TQ84_PKG_1", line 4
ORA-06512: at "RENE.TQ84_PKG_2", line 8
ORA-06512: at "RENE.TQ84_PKG_1", line 8
format_error_backtrace:
ORA-06512: at "RENE.TQ84_PKG_1", line 11
ORA-06512: at "RENE.TQ84_PKG_2", line 11
ORA-06512: at "RENE.TQ84_PKG_2", line 4
ORA-06512: at "RENE.TQ84_PKG_1", line 4
ORA-06512: at "RENE.TQ84_PKG_2", line 8
ORA-06512: at "RENE.TQ84_PKG_1", line 8
ORA-06512: at line 2
Both are quite similar.
format_error_stack includes an
SQL code (for example
ORA-06512) and an error message (compare with
sqlerrm and sqlcode) which is missing in
format_error_backtrace. On the other hand, the last line of
format_error_backtrace is missing in
format_error_stack.
The general format of the result is:
ORA-xxxxx: as yyy.zzzz, line nn <-- Line in second exception handler that re-raised exception
ORA-xxxxx: as yyy.zzzz, line nn <-- Line in first exception handler that re-raised exception
ORA-xxxxx: as yyy.zzzz, line nn <-- Line where error was caused
ORA-xxxxx: as yyy.zzzz, line nn <-- Line that called procedure with error
ORA-xxxxx: as yyy.zzzz, line nn <-- Line that called procedure which then called procedure with error
ORA-xxxxx: as yyy.zzzz, line nn <-- …
As can be seen: because of the nestedness of these lines, the line where the real error has occurred cannot be found without manual inspection of all lines that are listed in the output.
create or replace procedure print_stacks(id varchar2) is begin
dbms_output.put_line('Exception in ' || id);
dbms_output.put_line('format_error_stack');
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line('format_error_backtrace');
dbms_output.put_line(dbms_utility.format_error_backtrace);
-- dbms_output.put_line('format_call_stack');
-- dbms_output.put_line(dbms_utility.format_call_stack);
end;
/
create package tq84_pkg_1 as
procedure entry;
procedure p2;
end tq84_pkg_1;
/
create package tq84_pkg_2 as
procedure p1;
procedure cause_error;
end tq84_pkg_2;
/
create package body tq84_pkg_1 as
procedure p2 is begin
tq84_pkg_2.cause_error;
end p2;
procedure entry is begin
tq84_pkg_2.p1;
exception when others then
print_stacks('tq84_pkg_1.entry');
raise;
end entry;
end tq84_pkg_1;
/
create package body tq84_pkg_2 as
procedure cause_error is begin
execute immediate 'error';
end cause_error;
procedure p1 is begin
tq84_pkg_1.p2;
exception when others then
print_stacks('tq84_pkg_2.p1');
raise;
end p1;
end tq84_pkg_2;
/
begin
tq84_pkg_1.entry;
exception when others then
print_stacks('anonymous_block');
end;
/
drop package tq84_pkg_1;
drop package tq84_pkg_2;
drop procedure print_stacks;
exit