Below is part of the code for a procedure using 2 forall dmls i.e insert and update.
If any txn in bulk insert has failed in bulk_exceptions then the coresponding update txn in forall should not be commited or not take place.
Pl help to achieve the data integrity .
CREATE OR REPLACE PROCEDURE SP_XXX AS
BEGIN
FORALL i in INDICES OF T1_MAP SAVE EXCEPTIONS
INSERT INTO TABLE1
(
col_list.. )
VALUES
(
values list );
EXCEPTION
WHEN dml_errors THEN
error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..error_cnt LOOP
error_cd := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
error_msg := sqlerrm(-error_cd);
--dbms_output.put_line('error : '||replace(to_char(sqlerrm(-error_cd)),chr(10),' '));
INSERT INTO my_log
(
TABLE_ID,
SQL_CODE,
SQL_ERROR,
REMARKS,
TIME_STAMP
)
VALUES
(
9002,
error_cd,
error_msg,
'SP_XXX',
systimestamp
);
END LOOP;
END;
BEGIN
FORALL i in INDICES OF T1_MAP SAVE EXCEPTIONS
UPDATE T2
SET GROUP_TXN_NO=T1_MAP(i).MAP_SEQ_NO
WHERE condition..
EXCEPTION
WHEN dml_errors THEN
error_cnt := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1..error_cnt
LOOP
error_cd := SQL%BULK_EXCEPTIONS(i).ERROR_CODE;
error_msg := sqlerrm(-error_cd);
INSERT INTO my_log
(
TABLE_ID,
SQL_CODE,
SQL_ERROR,
REMARKS,
TIME_STAMP
)
VALUES
(
9003,
-error_cd,
error_msg,
'SP_XXX',
systimestamp
);
END LOOP;
END;
COMMIT;
END SP_XXX;
So you want to NOT update t2 for all the array elements that raised an exception when inserting to t1?
Just remove these array elements in the first exception handler for DML errors.
create table t1 (
c1 int, c2 int check ( c2 > 0)
);
create table t2 (
c1 int, c2 int
);
insert into t2 values ( 1, null );
insert into t2 values ( 2, null );
insert into t2 values ( 3, null );
commit;
set serveroutput on
declare
dml_errors exception;
pragma exception_init ( dml_errors, -24381 );
type trec is table of t1%rowtype
index by pls_integer;
vals trec;
begin
vals(1).c1 := 1;
vals(1).c2 := 1;
vals(2).c1 := 2;
vals(2).c2 := -99;
vals(3).c1 := 3;
vals(3).c2 := 42;
begin
forall i in 1 .. vals.count save exceptions
insert into t1 values ( vals(i).c1, vals(i).c2 );
exception
when dml_errors then
for err in 1 .. sql%bulk_exceptions.count loop
vals.delete ( sql%bulk_exceptions(err).error_index );
end loop;
end;
forall i in indices of vals
update t2
set c2 = vals(i).c2
where c1 = vals(i).c1;
dbms_output.put_line ( ' Rows ' || sql%rowcount );
end;
/
Rows 2
select * from t2;
C1 C2
1 1
2 <null>
3 42