PL/SQL Error Messages Part II

By | January 27, 2011

A continuation of a my previous post, I am looking more into the capabilities of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE. This time I will look more at what FORMAT_ERROR_BACKTRACE can do. These tests are on a 10.2.0.4 database.

First, I do a simple test to see what FORMAT_ERROR_BACKTRACE even returns:

BEGIN
    raise_application_error(-20001, 'Test Error Message');
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('BACKTRACE Output: '||dbms_utility.format_error_backtrace);
END;
BACKTRACE Output: ORA-06512: at line 2

Not really what I was expecting.. So it looks like to get the full message you need to combine both FORMAT_ERROR_STACK and FORMAT_ERROR_BACKTRACE. I guess that could explain why FORMAT_ERROR_STACK has the line break at the end:

BEGIN
    raise_application_error(-20001, 'Test Error Message');
EXCEPTION
    WHEN OTHERS
    THEN
        dbms_output.put_line('Output: '||dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
END;
Output: ORA-20001: Test Error Message
ORA-06512: at line 2

So now I want to look at how nesting my error through different procedures changes things:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: Test Error Message
ORA-06512: at line 5
ORA-06512: at line 11
ORA-06512: at line 14

As you can see from the output, FORMAT_ERROR_BACKTRACE does a good job of showing how the error was raised up through the various programs:

  • Line 14 is the call to p2()
  • Line 11 is the call to p1()
  • Line 5 is where the error originated

Now I want to look at how my output is changed by the often overlooked 3rd parameter of RAISE_APPLICATION_ERROR. This parameter is a boolean called keeperrorstack.

First with the default value (which is FALSE):

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			raise_application_error(-20001, 'New Message');
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: New Message
ORA-06512: at line 15
ORA-06512: at line 18

So, the default message makes it look like the first error that was ever raised was the ‘New Message’ error, and it came from line 15 (which is the raise_application_error call in the exception block of p2).

Now I will look at if the value is set to TRUE:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			raise_application_error(-20001, 'New Message', TRUE);
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: New Message
ORA-06512: at line 15
ORA-20001: Test Error Message
ORA-06512: at line 15
ORA-06512: at line 18

So now I can see that there was actually a second error message, however, it also appears to have originated from line 15, which is not correct. That is better but still less than ideal.

Last test… What happens when I just re-raise an error instead of raising a new error, which is much more likely for me to do:

declare
	procedure p1
	is
	begin
		raise_application_error(-20001, 'Test Error Message');		
	end p1;
--
	procedure p2
	is
	begin
		p1();	
	exception
		when others
		then
			RAISE;
	end p2;
begin
	p2();
exception
	when others
	then
		dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace);
end;
ORA-20001: Test Error Message
ORA-06512: at line 15
ORA-06512: at line 18

This is what I was afraid of. Now I keep my original error message, but again it looks like it originates from line 15, which is the RAISE; statement.

I would be interested to see if any of this changed in 11g, so if I get time I will run these tests again on an 11gR2 db.

Leave a Reply

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

Turn on pictures to see the captcha *