Hi All,
I've just come across a potential "gotcha" using bulk collect into. If the
bulk collect didn't "fill" up to limit, then <cursor>%notfound is set. I
expected to be able to loop through a cursor in chunks until all records had
been processed, but I lost the last chunk
Here's an example of how not to do it
create table t1 as select * from user_objects o where 1 = 2;
create or replace procedure p1
is
n number := 7;
type t1_rows_type is table of t1%rowtype;
t1_rows t1_rows_type := t1_rows_type();
cursor c1 is select * from user_objects;
begin
open c1;
loop
fetch c1 bulk collect into t1_rows limit n;
exit when c1%notfound;
-- exit when t1_rows.count = 0;
forall i in 1 .. t1_rows.count
insert into t1 values t1_rows(i);
dbms_output.put_line(sql%rowcount||' row(s) inserted');
commit;
end loop;
if c1%isopen then
dbms_output.put_line('c1 closed');
close c1;
end if;
end;
/
SQL> select count(*) from user_objects;
COUNT(*)
----------
17
SQL> exec p1;
7 row(s) inserted
7 row(s) inserted
c1 closed
Ohps,... 3 rows missing
Simple fix, but didn't expect to have to do this.
Use:
exit when t1_rows.count = 0;
Instead of
exit when c1%notfound;
Anyone else been caught out by this? Is there a better exit clause to use?
Cheers
Tony
Env: 10.2.0.4.0 and 11.1.0.6.0
Tested on 10.2.0.4.0 and 11.1.0.6
