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
