Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Complete FORMAT_ERROR_STACK

Thorsten KettnerApr 30 2020 — edited Apr 30 2020

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

Post Details

Added on Apr 30 2020
4 comments
9,236 views