Oracle 使用批量处理时错误消息 (ORA-01400) 未完成

嘿，
假设您有一个具有null约束的列的表:

create table t1 (val number not null);
复制

使用SQL，你得到一个限定的错误消息:

insert into t1 values (null);
复制

--> ORA-01400: cannot insert NULL into ("SCHEMA"."T1"."VAL")

但是，如果您使用PL/SQL，则不会收到完整的错误消息:

create or replace package t1_api as 
  type t1_tt is table of t1%rowtype;
  procedure bulk_insert;
end t1_api;
/
create or replace package body t1_api as 
  procedure bulk_insert is
    l_data t1_tt:=t1_tt();
  begin
    l_data.extend(2);
    l_data(1).val:=999;
    l_data(2).val:=null;
    <>
    begin
      forall i in 1..l_data.count save exceptions
        insert into t1 values l_data(i);
    exception
      when others then
        for i in 1..sql%bulk_exceptions.count loop
          dbms_output.put_line( sqlerrm( -sql%bulk_exceptions(i).error_code ) );     
        end loop;
    end forall_loop_with_save_except;
    commit;
  end bulk_insert;
end t1_api;
/

exec t1_api.bulk_insert;
复制

--> ORA-01400: cannot insert NULL into ()

所以我的问题是如何检索受影响列名的信息 ？？？

使用稍微不同的错误捕获方法怎么样

SQL> exec   DBMS_ERRLOG.create_error_log(dml_table_name=>'T1')

PL/SQL procedure successfully completed.

SQL>
SQL> create or replace package body t1_api as
  2    procedure bulk_insert is
  3      l_data t1_tt:=t1_tt();
  4    begin
  5      l_data.extend(2);
  6      l_data(1).val:=999;
  7      l_data(2).val:=null;
  8
  9      forall i in 1..l_data.count
 10        insert into t1 values l_data(i) LOG ERRORS REJECT LIMIT UNLIMITED;
 11
 12      commit;
 13    end bulk_insert;
 14  end t1_api;
 15  /

Package body created.

SQL>
SQL> exec t1_api.bulk_insert;

PL/SQL procedure successfully completed.

SQL> select * from err$_t1
  2  @pr
==============================
ORA_ERR_NUMBER$               : 1400
ORA_ERR_MESG$                 : ORA-01400: cannot insert NULL into ("MCDONAC"."T1"."VAL")

ORA_ERR_ROWID$                :
ORA_ERR_OPTYP$                : I
ORA_ERR_TAG$                  :
VAL                           :

PL/SQL procedure successfully completed.
复制