Forum Stats

  • 3,824,946 Users
  • 2,260,442 Discussions



Thorsten Kettner
Thorsten Kettner Member Posts: 42 Red Ribbon
edited Apr 30, 2020 3:31AM in Database Ideas - Ideas

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


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


ORA-01476: divisor is equal to zero




ORA-06512: at "SCHEMA_NAME.LIB_ERRORTEST", line 10



ORA-01476: divisor is equal to zero


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: (I also made a demo in Oracle Live SQL, but it has not been acceppted yet. I will add it when it is.)

Thorsten KettnerWilliam RobertsonGregVSven W.berxUser_RR3PW
6 votes

Active · Last Updated