Errorhandling in BULK INSERT FORALL

Errorhandling in BULK INSERT FORALL with error_index and bulk_exceptions

Additional Info:
To avoid Termination after a error By Steven Feuerstein


select * from user_source;
desc user_source;

create table bulk_error_test as select * from user_source where 1=0;

-- add check-constraint to provoke error
alter table bulk_error_test add constraint bulk_error_test_chk check (line > 1);
-------------------------------------------
set serveroutput on;
DECLARE
type typeTblData is table of bulk_error_test%ROWTYPE;
tblData typeTblData;
type typeTblID is table of integer;
tblID typeTblID;
bulk_error exception;
PRAGMA EXCEPTION_INIT (bulk_error, -24381);
nCnt number:=0;
cursor curData is select name,type,line,text from user_source;
BEGIN

OPEN curData;
LOOP
fetch curData bulk collect into tblData limit 500;
exit when tblData.COUNT = 0;

forall ii in tblData.FIRST..tblData.LAST save exceptions
insert into bulk_error_test values tblData(ii);

nCnt:=nCnt+sql%rowcount;

commit;
END LOOP;

CLOSE curData;

dbms_output.put_line ('Anzahlz Inserts ' || nCnt);

EXCEPTION

when bulk_error then
dbms_output.put_line('bulk_error');
for jj in 1 .. sql%bulk_exceptions.count
loop
dbms_output.put_line(
substr(
' Name: '||tblData(SQL%BULK_EXCEPTIONS(jj).error_index ).name ||
' Type: '||tblData(SQL%BULK_EXCEPTIONS(jj).error_index ).type ||
' Line: '||tblData(SQL%BULK_EXCEPTIONS(jj).error_index ).line ||
' Error_Index-ID:'||to_char(SQL%BULK_EXCEPTIONS(jj).error_index)||
' ERROR: '||sqlerrm(-SQL%BULK_EXCEPTIONS(jj).error_code),1,2000)||
' ERROR: '||DBMS_UTILITY.FORMAT_ERROR_STACK
);
end loop;
if curData%ISOPEN then close curData; end if;
raise;

when others then
dbms_output.put_line('others');
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_STACK);
raise;
END;

/*
Error report -
ORA-24381: Fehler in Array-DML
ORA-06512: in Zeile 47
24381. 00000 - "error(s) in array DML"
*Cause: One or more rows failed in the DML.
*Action: Refer to the error stack in the error handle.
bulk_error
Name: UT_UTREPORT Type: PACKAGE BODY Line: 1 Error_Index-ID:1 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
Name: UT_UTREPORT Type: PACKAGE Line: 1 Error_Index-ID:148 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
Name: UT_UTOUTPUT Type: PACKAGE Line: 1 Error_Index-ID:157 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
Name: UTOUTCOME Type: PACKAGE Line: 1 Error_Index-ID:198 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
Name: UT_UTOUTPUT Type: PACKAGE BODY Line: 1 Error_Index-ID:253 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
Name: UTPIPE Type: PACKAGE BODY Line: 1 Error_Index-ID:471 ERROR: ORA-02290: CHECK-Constraint (.) verletzt ERROR: ORA-24381: Fehler in Array-DML
*/

select * from bulk_error_test;

truncate table bulk_error_test drop storage;

drop table bulk_error_test purge;

Leave a Reply

Your email address will not be published. Required fields are marked *