Skip to Main Content
  • Questions
  • Maintain data interity in transaction with multiple forall dml statements

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: March 14, 2020 - 7:59 am UTC

Last updated: March 17, 2020 - 10:41 am UTC

Version: 12c

Viewed 1000+ times

You Asked

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;

and Chris said...

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 


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library