Oracle Tips and Tricks — David Fitzjarrell

March 4, 2008

What Was That Masked Message?

Filed under: pl/sql — dfitzjarrell @ 13:53

Lately the topic for discussion in the better Oracle-related blogs (Tom Kyte’s blog, Oracle WTF) is the use of RAISE_APPLICATION_ERROR in the WHEN OTHERS catch-all exception handler. Many of the implementations (and I use the term loosely) involve producing some mangled error text having nothing at all to do with the error which generated it. I’ve created an example to illustrate this point. First let’s set up the table and key we’ll use to produce our glorious error messages:

SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table pktest(
2 pk number,
3 val1 number,
4 val2 number,
5 val3 number
6 );

Table created.

SQL> SQL> -- SQL> -- Add the primary key SQL> -- SQL> alter table pktest add constraint pktestpk primary key (pk);

Table altered.

SQL> SQL> -- SQL> -- Insert data SQL> -- SQL> begin 2 for i in 1..25 loop 3 insert into pktest 4 values(i,i+1,i+2,i+3); 5 end loop; 6 end; 7 /

PL/SQL procedure successfully completed.

SQL> SQL> -- SQL> -- Commit the data SQL> -- SQL> commit;

Commit complete.

SQL>

Okay, we have a table with a primary key, loaded with sample data. Let’s try to insert a record with an existing key and see what happens. Oh, and we’ll mangle the error message to produce something quite useless (which, unfortunately, happens all too often in production code in a misguided effort to make the error text, ummm, ‘user friendly’):

SQL> --
SQL> -- Try and insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Intentionally obscure the original
SQL> -- error
SQL> --
SQL> begin
2 insert into pktest values (1,2, 3, 4);
3 exception
4 when others then
5 raise_application_error(-20999, 'Sumthin'' bad happened');
6 end;
7 /
begin
*
ERROR at line 1:
ORA-20999: Sumthin' bad happened
ORA-06512: at line 5

SQL>

We know it’s a constraint violation because that’s what we intentionally executed. What we didn’t intend (but happened anyway) was to so obscure the original error as to make the text we displayed meaningless. The intention was good, but the Road to Hell is paved with such ‘goodness’. Using RAISE_APPLICATION_ERROR without fully investigating all of the implementation options causes this to happen, as it essentially ‘throws away’ the error stack, by default, and stops with our gloriously simple yet thoroughly confusing message. RAISE_APPLICATION_ERROR, when provided with a value of TRUE for the third parameter (yes, there are three parameters one can pass) replaces the error message but preserves the rest of the error stack. To illustrate:

SQL> --
SQL> -- Again try to insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Provide a different error text
SQL> -- but preserve the rest of the
SQL> -- error stack
SQL> --
SQL> -- Note the error is listed as the
SQL> -- line executing the 'raise_application_error'
SQL> -- call, not the line where the error
SQL> -- actually occurred
SQL> --
SQL> -- Oracle 10.2.0.3, Solaris 9
SQL> --
SQL> begin
2 insert into pktest values (1,2, 3, 4);
3 exception
4 when others then
5 raise_application_error(-20999, 'Sumthin'' bad happened',true);
6 end;
7 /
begin
*
ERROR at line 1:
ORA-20999: Sumthin' bad happened
ORA-06512: at line 5
ORA-00001: unique constraint (BING.PKTESTPK) violated

SQL>

And we get the rest of the error stack so we know what sort of error created the problem. Unfortunately we still don’t know where, exactly, that error occurred because RAISE_APPLICATION_ERROR reports, apparently, the line where it was called as the error source. We can fix this by choosing to not use RAISE_APPLICATION_ERROR and, instead, use two functions from the DBMS_UTILITY package, namely FORMAT_ERROR_STACK (available in 9i and later releases) and FORMAT_ERROR_BACKTRACE (available in 10g and later releases):

SQL> --
SQL> -- Again try to insert an existing key
SQL> -- value into the test table
SQL> --
SQL> -- Provide a different error text
SQL> -- but preserve the rest of the
SQL> -- error stack
SQL> --
SQL> -- Using a different mechanism
SQL> -- the error line for the insert
SQL> -- is finally reported as the source
SQL> --
SQL> -- Oracle 10.2.0.3, Solaris 9
SQL> --
SQL> begin
2 insert into pktest values (1,2, 3, 4);
3 exception
4 when others then
5 -- Output desired error message
6 dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
7 -- Output actual line number of error source
8 dbms_output.put(dbms_utility.format_error_backtrace);
9 -- Output the actual error number and message
10 dbms_output.put_line(dbms_utility.format_error_stack);
11 end;
12 /
-20999: Sumthin' bad happened -- error stack follows
ORA-06512: at line 2
ORA-00001: unique constraint (BING.PKTESTPK) violated

PL/SQL procedure successfully completed.

SQL>

Now we get the line where the error occurred; unfortunately FORMAT_ERROR_BACKTRACE doesn’t provide the error message, which is why we also used the FORMAT_ERROR_STACK function.

To see how FORMAT_ERROR_BACKTRACE can provide information not otherwise available we’ll set up another example, a procedure called by a procedure called by, yes, a procedure. Let’s see if FORMAT_ERROR_BACKTRACE will correctly inform us of the location of the actual error:

SQL> create or replace procedure myproc
  2  is
  3  begin
  4        --
  5        -- Informative text to the end user
  6        --
  7        dbms_output.put_line('Happily executing myproc');
  8        --
  9        -- But let's raise an error anyway
 10        --
 11        raise no_data_found;
 12  end;
 13  /

Procedure created.

SQL> SQL> create or replace procedure yourproc 2 is 3 begin 4 -- 5 -- Yet more informative text 6 -- 7 dbms_output.put_line('Calling myproc'); 8 -- 9 -- A guaranteed error occurs here 10 -- 11 myproc; 12 end; 13 /

Procedure created.

SQL> SQL> create or replace procedure allproc 2 is 3 begin 4 -- 5 -- And another helpful message 6 -- 7 dbms_output.put_line('Calling yourproc'); 8 -- 9 -- Call the proc which calls the proc 10 -- that generates the guaranteed 11 -- error 12 -- 13 yourproc; 14 exception 15 -- 16 -- Let's handle the exception here 17 -- 18 when others then 19 dbms_output.put_line('Displaying the error stack:'); 20 dbms_output.put(dbms_utility.format_error_stack); 21 dbms_output.put_line(dbms_utility.format_error_backtrace); 22 end; 23 /

Procedure created.

SQL> SQL> -- SQL> -- Let's get this ball rolling SQL> -- SQL> -- The error text should point back to SQL> -- the source of the error (line 11 of myproc) SQL> -- SQL> SQL> exec allproc Calling yourproc Calling myproc Happily executing myproc Displaying the error stack: ORA-01403: no data found ORA-06512: at "BING.MYPROC", line 11 ORA-06512: at "BING.YOURPROC", line 11 ORA-06512: at "BING.ALLPROC", line 13

PL/SQL procedure successfully completed.

SQL>

Even though the error location was buried in the call stack FORMAT_ERROR_BACKTRACE found it (even three layers deep) and reported it. Nice. As noted earlier it was still necessary to include a call to FORMAT_ERROR_STACK to return the actual error encountered.

Funny how things work as intended when one follows the provided instructions.

Blog at WordPress.com.