Feuertip #20: sqlerrm mysteries

Like sqlcode, sqlerrm is a PL/SQL function with a very simple purpose in life: it “returns the error message associated with an error code.” https://docs.oracle.com/database/121/LNPLS/sqlerrm_function.htm#LNPLS01350

Which is true, as far it goes. That word “associated” is kind of interesting. Here’s another way to put it:

sqlerrm returns the error message associated with the error code passed to it. If no code is provided, then the value returned by sqlcode is used.

Like sqlcode, sqlerrm is both straightforward and also a bit mysterious. Not the least because Oracle also says: “DBMS_UTILITY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause.”

So, hey, like: “Don’t use it!”

Huh?

Let’s explore!

First, like sqlcode, you cannot call sqlerrm directly from within a SQL statement, even (and especially) one that is located inside a PL/SQL block. In other words, if you want to insert error information into your error log, you cannot do this:

exception 
   when others then
      insert into error_log (errcode, errmsg) values (sqlcode, sqlerrm);

Instead, you should call those functions, assign the values to PL/SQL variables, and then use those as in:

exception 
   when others then
      declare
         l_code integer := sqlcode;
         l_emsg varchar2(512) := sqlerrm;
      begin
         insert into error_log (errcode, errmsg) values (l_code, l_emsg);
      end;

Or, as I discovered (was informed by Niels Hecker), you can qualify the function names and then call them in SQL, as in:

exception 
   when others then
      insert into error_log (errcode, errmsg) values 
         (sys.standard.sqlcode, sys.standard.sqlerrm);

I still do not know why this works and I have not been able to get an explanation from Friends@Oracle, but it is what it is!

Next, why would Oracle recommend that you not use sqlerrm? Because (a) it only returns the error message, not the error stack, and (b) the string it returns is restricted to 512 bytes.

It is likely that you never even thought about error stacks. What else would you have or want to have besides the error message?

To see this, when I run this block….

declare
   procedure proc1 is
   begin
      raise no_data_found;
   end;
   
   procedure proc2 is
   begin
      proc1;
   exception
      when others then
         raise value_error;
   end;
   
   procedure proc3 is
   begin
      proc2;
   exception
      when others then
         raise program_error;
   end;
begin
   proc3;
exception
   when others then
      dbms_output.put_line(sqlerrm);
end;

I see:

ORA-06501: PL/SQL: program error

But if I replace the code in the handler to:

dbms_output.put_line(dbms_utility.format_error_stack);

then I see the following:

ORA-06501: PL/SQL: program error
ORA-06512: at line 23
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 23
ORA-01403: no data found
ORA-06512: at line 23

There’s the stack! It shows how the exception propagates out to the point at which you ask for the message/stack. Maybe you don’t care about that, but it can give you lots of insight into your code. So why not store the full stack instead of just the message?

And that’s why Oracle recommends you call dbms_utility.format_error_stack instead of sqlerrm. But that only makes sense for invocations of sqlerrm that do not pass an argument.

To understand what I mean, let’s go back to my definition of this built-in function:

sqlerrm returns the error message associated with the error code passed to it. If no code is provided, then the value returned by sqlcode is used.

Most developers probably don’t even realize you can pass an argument to sqlerrm. They simply call it to get the error message associated with the current error code (sqlcode) and then store that string in their error log (or display it).

But what if you have the error code and not the message? Then you can use sqlerrm to look up the “generic” message associated with that code.

Take a look at the output from this block:

declare
   l_codes dbms_sql.number_table := dbms_sql.number_table(
      0  => -1,
      1  => 100,
      2  => - 1403,
      3  => - 1422,
      4  => 1422,
      5  => 1,
      6  => -20000,
      7  => -21000,
      8  => -21002,
      9  => -50000
   );
begin
   for indx in l_codes.first..l_codes.last loop
      begin
         dbms_output.put_line('sqlerrm('||l_codes(indx)||') => ' || sqlerrm(l_codes(indx)));
      exception
         when others then
            dbms_output.put_line('Unable to call sqlerrm with code ' || l_codes(indx));
      end;
   end loop;
end;
/

sqlerrm(-1) => ORA-00001: unique constraint (.) violated
sqlerrm(100) => ORA-01403: no data found
sqlerrm(-1403) => ORA-01403: no data found
sqlerrm(-1422) => ORA-01422: exact fetch returns more than requested number of rows
sqlerrm(1422) =>  -1422: non-ORACLE exception 
sqlerrm(1) => User-Defined Exception
sqlerrm(-20000) => ORA-20000: 
sqlerrm(-21000) => ORA-21000: error number argument to raise_application_error of  is out of range
sqlerrm(-21002) => ORA-21002: Message 21002 not found;  product=RDBMS; facility=ORA
sqlerrm(-50000) => ORA-50000: Message 50000 not found;  product=RDBMS; facility=ORA

Here are the rules governing sqlerrm return strings:

• If the value of error_code is +100, SQLERRM returns ORA-01403.
• If the value of error_code is a positive number other than +100, SQLERRM returns this message:
• If the value of error_code is a negative number whose absolute value is an Oracle Database error code, SQLERRM returns the error message associated with that error code. For example:
• If the value of error_code is a negative number whose absolute value is not an Oracle Database error code, SQLERRM returns this message: ORA-error_code: Message error_code not found; product=RDBMS;
  facility=ORA

Hopefully you see what I mean by the “generic” error message.

You cannot use dbms_utility.format_error_stack in the same way. It accepts no arguments. It doesn’t look up error messages for you. It just returns the error stack. Which is a fine purpose in life to fulfill.

OK but what’s with FORALL?

Are you wondering why Oracle says: “DBMS_UTILITY.FORMAT_ERROR_STACK is recommended over SQLERRM, unless you use the FORALL statement with its SAVE EXCEPTIONS clause”? That’s because when the SQL engine passes an error back to the PL/SQL engine, the unsigned error code is saved for later inspection, but not the error message. Plus, you could have more than one error. So you will likely want to use sqlerrm to get the generic error message for that code. You can’t do that with dbms_utility.format_error_stack.