PL/SQL Error Messages

By | January 21, 2011

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Turn on pictures to see the captcha *