Systems Engineering and RDBMS

DBMS_UTLITY.FORMAT_ERROR_STACK

Posted by decipherinfosys on February 7, 2009

As the name suggests, the supplied DBMS_UTILITY package has some very useful procedures and functions that can be used for maintaining and debugging PL/SQL code.  One such procedure is FORMAT_ERROR_STACK.  A common use of this procedure is in exception handling scenarios to display error messages just like SQLERRM.  Here is an example:

DECLARE
a NUMBER(9);

BEGIN
SELECT col3
INTO a
FROM TEST;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(SQLERRM(-1428));
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
END;

Here is the output.

ORA-01422: exact fetch returns more than requested number of rows
ORA-01428: argument ” is out of range
ORA-01422: exact fetch returns more than requested number of rows

So what is the difference between SQLERRM and FORMAT_ERROR_STACK procedures?

SQLERRM displays only first 512 bytes of the error while FORMAT_ERROR_STACK displays entire error message. So Oracle recommends use of later in the PL/SQL code so that we get entire error message and not the truncated message. If you are using dbms_output package then you have to make sure that you are not constrained by serveroutput limitation. In 10g, Oracle lifted the earlier limitation of 255 bytes in ‘set serveroutput on’ command.

Also SQLERRM can take a specific error code as an argument and that is the reason we have used it twice, one with the argument and one without an argument.  For clarity purposes, we have given a different error code.  It also indicates that when an argument is passed to SQLERRM, it overrides the actual exception code.  FORMAT_ERROR_STACK procedure doesn’t take any argument.

Even though the procedure name indicates STACK, it has nothing to do with the stack. There are other procedures in dbms_utility package, which are used to create the stack trace of the error.  We will cover that in a future blog post.

One Response to “DBMS_UTLITY.FORMAT_ERROR_STACK”

  1. […] by decipherinfosys on February 10, 2009 In one of our previous blog post,  we covered the format_error_stack procedure of the dbms_utility package to display errors in […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: