Thanks for the question, Markus.
Asked: August 08, 2019 - 1:38 pm UTC
Last updated: August 12, 2019 - 3:40 am UTC
Version: 11.2.0.4.0
Viewed 1000+ times
create table t1 (val number not null);
insert into t1 values (null);
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; <<forall_loop_with_save_except>> 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;
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.
Markus, August 09, 2019 - 1:06 pm UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library