save exceptions handling in two-phase update example; 9.2.0.7

From: Cosmin Ioan <cosmini_at_bridge-tech.com>
Date: Thu, 17 May 2007 11:55:31 -0700 (PDT)
Message-ID: <465194.22367.qm@web60414.mail.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 17 2007 - 13:55:31 CDT