SQLERRM, use it with caution.

问题由来

客户发过来一个request, 说调用某个接口的时候出现了一堆的错误日志，但是从给出的信息很难知道什么地方出错了。从发过来的错误日志中可以看出一堆的‘ ORA-20002’ 的错误信息，但是没有具体的error message! 很显然， 这个是程序中自定义的错误，所以这个日志信息几乎是没有任何价值可言。

不过幸运的是，从日志中可以判断出来出错的位置在于通过FORALL方式把一个集合(associate array)中的数据批量插入一张表，代码如下...

begin
        forall i in g_insert_auth_table.first .. g_insert_auth_table.last save exceptions
            insert into gem_orig_authorization
            values g_insert_auth_table(i);
    exception
        when others then
            for err in 1 .. sql%bulk_exceptions.count
            loop
                add_update_imp(    g_insert_imp_rowid_table(sql%bulk_exceptions(err).error_index),
                                sqlerrm(-sql%bulk_exceptions(err).error_code) );
                pack_gem_common.log_generic('flush_insert_auth',
                            vv_message2 => sqlerrm(-sql%bulk_exceptions(err).error_code),
                            vv_message1 => 'Error while inserting gem_orig_authorization of combination' ||
                                           g_insert_auth_table(sql%bulk_exceptions(err).error_index)
                                          .combination_id || '');
            end loop;
    end;

错误信息显示的就是vv_message2 => sqlerrm(-sql%bulk_exceptions(err).error_code) 这个参数显示的。很显然，这个是通过SQLERRM函数来获取error message的，但是杯具的是因为-20002是用户自定义的错误,不是oracle标准的error code， 因此通过SQLERRM方式来获取error message是肯定不行的，

SQL> begin
  2   dbms_output.put_line(sqlerrm(-20002));
  3  end;
  4  /
ORA-20002:

不过分析上面的这段代码，肯定是在插入数据的时候出现问题了，通过分析这张表上的trigger，最终定位到是数据违背了该表上定义的一个constraint! 虽然trigger中会抛出具体的错误信息，但是在捕获的时候因为用了错误的方式导致这个错误信息就丢失了！因此在使用SQLERRM的时候一定要注意，要判断error code是不是标准的oracle error code才行。

 从文档 http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2201 中可以看到如下这段话...

All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:

%BULK_EXCEPTIONS(i).ERROR_INDEX holds the iteration of the FORALL statement during which the exception was raised.

%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.

The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.

The individual error messages, or any substitution arguments, are not saved, but the error message text can looked up using ERROR_CODE with SQLERRM

 个人觉得oracle这点做得不够好，应该在SQL%BULK_EXCEPTIONS中再加入一个field，用来保存error_message, 这对于用户自定义的error message非常有帮助，否则像遇到的这个问题就会搞得error message丢失了！

当然我们可以自己搞个workaround，自己定义一个error_message的table，用来保存用户自定义的error_message，这样在处理的时候就可以获得我们自己定义的error message了.... (当然这个例子不是很合理，完全可以用一个constraint来代替trigger, 这样就可以用SQLERRM了，这里只是演示作用)

create table fang_error_msg(error_code int, error_msg varchar2(100));
insert into fang_error_msg values(-20002, 'code should be less than 10!');
commit;

create table fang_test(code int);

create trigger tri_fang_test 
before insert on fang_test
for each row
begin
  if :new.code > 10 then
     raise_application_error(-20002,  'blah blah...');
  end if;
end;
/

declare
   type t_int_table is table of pls_integer index by pls_integer;
   l_code_table t_int_table;
begin
  for i in 5..15 loop
      l_code_table(i) := i;
  end loop;
  
  forall i in l_code_table.first..l_code_table.last  save exceptions
    insert into fang_test values (l_code_table(i));

exception
   when others then
       declare
          l_error_code pls_integer;
          l_error_message varchar2(2000);
       begin
           for err in 1..sql%bulk_exceptions.count loop
              l_error_code := -sql%bulk_exceptions(err).error_code;
              
              if l_error_code between -20999 and -20000 then
                  select error_msg into l_error_message from fang_error_msg where error_code=l_error_code;
              else
                 l_error_message := sqlerrm(l_error_code);
              end if;
             
              dbms_output.put_line(l_error_message);
           end loop;
       end;
end;
/

关于SQLERRM (http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqlerrm_function.htm)

 SQLERRM是一个pl/sql function,而不是sql function, 因此不能用在SQL语句中，只有通过pl/sql方式来调用，如下所示...

SQL> select sqlerrm(-1555) from dual;
select sqlerrm(-1555) from dual
       *
ERROR at line 1:
ORA-00904: "SQLERRM": invalid identifier


SQL> begin
  2    dbms_output.put_line(sqlerrm(-1555));
  3  end;
  4  /
ORA-01555: snapshot too old: rollback segment number  with name "" too small

文档上关于SQLERRM有这么一段notes...

SQLERRM is especially useful in the OTHERS exception handler, where it lets you identify which internal exception was raised. The error number passed to SQLERRM should be negative. Passing a zero to SQLERRM always returns the ORA-0000: normal, successful completion message. Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, in which case SQLERRM returns the ORA-01403: no data found message.

注意这里面有句话--Passing a positive number to SQLERRM always returns the User-Defined Exception message unless you pass +100, 也就是说所有不等于100的正的error code都会返回'User-Defined Exception'这样的error message, 这个与实际不相符，貌似只有error code为1的时候才会返回这个信息，其他值的时候会返回'-NNNNN: non-ORACLE exception'这样的信息...

SQL> exec dbms_output.put_line(sqlerrm(1));
User-Defined Exception

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sqlerrm(2));
-2: non-ORACLE exception

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sqlerrm(3));
-3: non-ORACLE exception

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sqlerrm(10000));
-10000: non-ORACLE exception

 比较特殊的几个“error code" -- 0, 100,

SQL> exec dbms_output.put_line(sqlerrm(0));
ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sqlerrm(100));
ORA-01403: no data found

PL/SQL procedure successfully completed.

SQL>

如果error code是负数，但是不是valid的话，那么错误信息是‘ORA-NNNNN: Message NNNNN not found; product=RDBMS; facility=ORA’，

SQL> exec dbms_output.put_line(sqlerrm(-2));
ORA-00002: Message 2 not found;  product=RDBMS; facility=ORA

PL/SQL procedure successfully completed.

SQL> exec dbms_output.put_line(sqlerrm(-3));
ORA-00003: Message 3 not found;  product=RDBMS; facility=ORA

PL/SQL procedure successfully completed.

SQL>

关于DBMS_UTILITY.FORMAT_ERROR_STACK

跟SQLERRM类似的还有一个函数，叫DBMS_UTILITY.FORMAT_ERROR_STACK，而且一般是推荐用这个函数来获取错误信息，因为这个函数返回的字符串最多支持2000个字符，而SQLERRM只有255个字符。

DBMS_UTILITY.FORMAT_ERROR_STATCK不接受error code参数，因此返回的是最近抛出的一个错误的信息...

SQL> begin
  2     raise NO_DATA_FOUND;
  3  exception
  4     when no_data_found then
  5        dbms_output.put_line(dbms_utility.format_error_stack);
  6  end;
  7  /
ORA-01403: no data found

 如果没有错误抛出，直接调用这个函数，应该是啥都没有...

SQL> exec dbms_output.put_line(nvl(dbms_utility.format_error_stack, 'nothing wrong happened!'));
nothing wrong happened!

PL/SQL procedure successfully completed.