I have been aware of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE for some time now, but until recently have never found myself needing to know much about them. For my uses SQLERRM has always worked just fine. However, in a project I am currently working on, we are looking to have better error reporting, so it was finally time to look at what these functions can do. This will be a two part posting on what I find. This part will cover the differences between SQLERRM and FORMAT_ERROR_STACK.
In one of Steven Feuerstein‘s presentations, he mentions how DBMS_UTILITY.FORMAT_ERROR_STACK is preferred since SQLERRM could possibly truncate your error messages, but I had never seen this in practice so I threw together a quick test:
BEGIN raise_application_error(-20001, rpad('test', 10000, '!')); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('SQLERRM: '||length(sqlerrm)); dbms_output.put_line('FORMAT_ERROR_STACK: '||length(dbms_utility.format_error_stack)); END;
SQLERRM: 510 FORMAT_ERROR_STACK: 1899
So clearly they will both cut off some information, but FORMAT_ERROR_STACK will give you 3 times as much. While I was playing around
with this, I saw another anomaly:
BEGIN raise_application_error(-20001, rpad('test', 10, '!')); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('SQLERRM: '||length(sqlerrm)); dbms_output.put_line('FORMAT_ERROR_STACK: '||length(dbms_utility.format_error_stack)); END;
SQLERRM: 21 FORMAT_ERROR_STACK: 22
FORMAT_ERROR_STACK contains an extra character even when the message is short enough that it shouldn’t be getting truncated, so that made me curious as to what was going on:
DECLARE err_msg VARCHAR2(255); v1 VARCHAR2(255); v2 VARCHAR2(255); BEGIN raise_application_error(-20001, rpad('test', 10, '!')); EXCEPTION WHEN OTHERS THEN -- store sqlerrm in variable so I can use it in SQL to take advantage of dump function -- since I don't know where extra character is err_msg := sqlerrm; -- Get results of dump select dump(err_msg), dump(dbms_utility.format_error_stack) INTO v1, v2 from dual; -- display results of dump dbms_output.put_line(rpad('SQLERRM:', 20)||v1); dbms_output.put_line(rpad('FORMAT_ERROR_STACK:', 20)||v2); END;
SQLERRM: Typ=1 Len=21: 79,82,65,45,50,48,48,48,49,58,32,116,101,115,116,33,33,33,33,33,33 FORMAT_ERROR_STACK: Typ=1 Len=22: 79,82,65,45,50,48,48,48,49,58,32,116,101,115,116,33,33,33,33,33,33,10
As you can see from the output, the extra character is the 10 at the end which corresponds to a line feed. Something to keep in mind if you are trying to add the error message to a standardized format (like possibly writing into a file) you may need to remove that line feed first.
Also in my testing I discovered that you can’t use SQLERRM in SQL but you can use FORMAT_ERROR_STACK. I don’t really know how that would be useful, but who knows…
The next part will focus mostly on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to determine what information it can give you and in what situations it loses information.