RE: bulk collect into ... limit N
- From: Herald ten Dam <Herald.ten.Dam@xxxxxxxxxxxxxxx>
- To: "tony.adolph.dba@xxxxxxxxx" <tony.adolph.dba@xxxxxxxxx>
- Date: Wed, 1 Jul 2009 06:59:58 +0200
Hi,
Steven Feuerstein wrote it in the Oracle magazine:
http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html. It is the
way Oracle works.
Herald ten Dam
Superconsult
________________________________
Van: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] namens Tony
Adolph [tony.adolph.dba@xxxxxxxxx]
Verzonden: woensdag 1 juli 2009 0:05
Aan: Ian Cary
CC: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
Onderwerp: Re: bulk collect into ... limit N
Hi Ian & Alex,
Although the exit when c1%notfound; at the end of the loop works... I can't get
my head around why it does. See questions as comments in line....
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;
/*
why is c1%notfound = TRUE here when there are still rows
that haven't been fetched.
Each of the 1st 2 passes 7 rows are fetched, leaving 3 rows.
On the 3rd pass 3 rows are fetched, but %notfound
is set true (here).. *but* it still FALSE at the end
of the loop. The cursor hasn't been re-fetched so
whats happening?
I dont understand the mechanism here.
*/
-- 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;
-- (from Alex)
exit when c1%notfound;
-- from Ian (and also works, thanks)
-- exit when t1_rows.count < n;
end loop;
if c1%isopen then
dbms_output.put_line('c1 closed');
close c1;
end if;
end;
SQL> exec p1;
7 row(s) inserted
7 row(s) inserted
3 row(s) inserted
c1 closed
Other related posts:
