Complete FORMAT_ERROR_STACK

Thorsten Kettner

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.)

Thorsten Kettner

Here is the demo in Oracle Live SQL: https://livesql.oracle.com/apex/livesql/s/J0WFL67YI2SPED5M2Q2JDL7UP

Sven W.

Here is the demo in Oracle Live SQL: https://livesql.oracle.com/apex/livesql/s/J0WFL67YI2SPED5M2Q2JDL7UP

Yes this is a problem. I created a package syn_utility that circumvents this by eliminating the duplicated entries.
See the code and some examples on LiveSql: https://livesql.oracle.com/apex/livesql/s/gtdi3umkyb450qaans1e4sdxi

Here is an example how it works.

You see that in the output are a lot of duplicated lines.

and when replasing dbms_utility with the matching syn_utility package, then this will be returned:

Sven W.

Another point is that SQLERRM now might be the better choise. It is not limited to 512 chars anymore. It was in much older DB versions.

Thorsten Kettner

Another point is that SQLERRM now might be the better choise. It is not limited to 512 chars anymore. It was in much older DB versions.

Thank you. I only read this now. The limitation of 512 bytes is still documented in the Oracle 19 docs here. They say, however, that this is the maximum length for an Oracle error message anyway and I have not found any example on the Internet where SQLERRM really cropped an error message. I can only guees now that they once tried to add the information where the error occured to SQLERRM, for which the 512 bytes were too short, and then invented FORMAT_ERROR_BACKTRACE for this.

That would really mean that you are right and we could work now with SQLERRM (which is now again the mere error message) and FORMAT_ERROR_BACKTRACE (which is now the complete error stack) alone and don't need FORMAT_ERROR_STACK at all.

So yes, SQLERRM seems the better choice now.