Skip navigation

Complete FORMAT_ERROR_STACK

score 50
You have not voted. Active

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

Comments

Vote history