save exceptions handling in two-phase update example; 9.2.0.7

hi all,
  I'm trying to create a basic prototype for a problem updating a large target 
table based on another large source table, and upon completion, to update a 
flag on the source table that the initial update went ok.  Sorry abt the font & 
spacing ... hope it's still readable.
   
  My question/quandary is, it works well if updates are successful or 
rowcount=0, however this two phase update does not work ok when there are 
exceptions in the target update...  I'm sure it's a silly programatic error 
that I'm making  ;-)
  any help is much appreciated,
   
  I don't want to do a set based  update.... where millions or more records are 
involved, might criple an otherwise close-to-capacity busy OLTP system  .... ;-)
   
  thx much for any feedback,
  Cosmin
   
   
  drop table tbl_source
  /
  drop table tbl_target
  /
  create table tbl_source
  as select rownum as rownumber,
  1 as col2
  from table_x_call_trans
  where rownum<101
  /
  create table tbl_target
  as select rownum as rownumber,
  mod(rownum,10) as col2
  from table_x_call_trans
  where rownum<101
  / 
  alter table tbl_TARGET
  add constraint COL2_SM10
  check (col2<11)
  / 
  
  create unique index tbl_target_idx on tbl_target(rownumber)
  /
  create unique index tbl_source_idx on tbl_source(rownumber)
  /
   
   
  declare
  type tbl is table of pls_integer;
  bulk_errors exception;
  pragma exception_init(bulk_errors, -24381);
  
  v1 tbl;
  v2 tbl;
  v3 tbl:= tbl();
  v4Err tbl:= tbl();
 
  i pls_integer;
  n_limit pls_integer:=50;
  
  cursor c is
  select rownum, col2 from tbl_source;  
  
begin
    open c;
  loop
    fetch c bulk collect into v1, v2 limit n_limit;
    
    begin
      --try to update target
      forall i in 1..v1.count save exceptions
        update tbl_target ct set col2=2 where ct.rownumber=v1(i);
         --update tbl_target ct set col2=col2* 2 where ct.rownumber=v1(i);  -- 
simulate exceptions....
      
      --get all the correctly updated records
        --works fine if no exceptions come up...when I simulate exceptions, the 
commented line above, I need to handle these accurately in the source table, 
ie, not update the source table flag...
  
      v3.delete;
      for i in 1..v1.count loop
        v3.extend;
        v3(i):= sql%bulk_rowcount(i);
      end loop;  
    
      --don't think this works properly
    /*exception when bulk_errors then
      v4Err.extend;
      for i in 1..sql%bulk_exceptions.count loop
        v4Err.extend;
        v4Err(i):= sql%bulk_exceptions(i).error_index;
        
      end loop;*/
    end;
    
    --update back in source, if updates in target successful;  works fine if no 
exceptions....
    forall i in 1..v3.count
      update tbl_source cs set col2=0 where cs.rownumber=v1(i) and  v3(i)=1;
    
    commit;
    exit when c%notfound;
  end loop;
  close c;
end;  
/
   
  I don't want to do a set based  update.... where millions or more records are 
involved, might criple an otherwise sluggish OLTP busy system.... ;-)
   
  thx much for any feedback,
  Cosmin

Other related posts:

  • » save exceptions handling in two-phase update example; 9.2.0.7