I've just moved from Oracle 11g to Oracle 19c and noticed that DBMS_UTILITY.FORMAT_ERROR_STACK has become much more talkative. Originally FORMAT_ERROR_STACK was meant to replace SQLERRM, because the latter would mutilate error messages longer than 512 characters. DBMS_UTILITY.FORMAT_ERROR_BACKTRACE on the other hand contained and still contains the calling path that lead to an exception. In my packages I would hence have an excpetion block reporting both FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE.
In Oracle 19c, however, FORMAT_ERROR_STACK returns the error message plus part of the back trace. If in my packages I report both FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE, I will issue a lot of duplicate information hence. (And as I combine both in one string, this even leads to a wrong path now looking like going through some routines twice.)
This forces me to report SQLERRM and FORMAT_ERROR_BACKTRACE instead now, well knowing that I may not get the complete error message. Why does FORMAT_ERROR_STACK include the back trace but mutilate it? In my opinion the function doesn't adequately replace SQLERRM anymore, nor does it replace FORMAT_ERROR_BACKTRACE (yet).
Request: Please either make FORMAT_ERROR_STACK only return the error message as before or return the error message plus the complete back trace path. (Which of the two, I personally don't care, because I want both the error message and the back trace path anyway. If others have a preference, please put it in the comments.)
Example:
create or replace package lib_errortest as
procedure proc1;
end lib_errortest;
create or replace package body lib_errortest as
procedure proc2 as
v_number integer;
begin
v_number := 1 / 0;
end;
procedure proc1 as
begin
proc2;
exception when others then
dbms_output.put_line('SQLERRM');
dbms_output.put_line(sqlerrm);
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('FORMAT_ERROR_BACKTRACE');
dbms_output.put_line(dbms_utility.format_error_backtrace);
dbms_output.put_line('---------------------------------------');
dbms_output.put_line('FORMAT_ERROR_STACK');
dbms_output.put_line(dbms_utility.format_error_stack);
end;
end lib_errortest;
begin
lib_errortest.proc1;
end;
This returns
SQLERRM
ORA-01476: divisor is equal to zero
---------------------------------------
FORMAT_ERROR_BACKTRACE
ORA-06512: at "SCHEMA_NAME.LIB_ERRORTEST", line 5
ORA-06512: at "SCHEMA_NAME.LIB_ERRORTEST", line 10
---------------------------------------
FORMAT_ERROR_STACK
ORA-01476: divisor is equal to zero
ORA-06512: at "SCHEMA_NAME.LIB_ERRORTEST", line 5
As you can see, FORMAT_ERROR_STACK returns the error message and part of the back trace, missing the last line (the function that was initially called).
A demo here: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=c253443e9c5690225aaa8c9b8e886f27 (I also made a demo in Oracle Live SQL, but it has not been acceppted yet. I will add it when it is.)