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


Steven Feuerstein wrote it in the Oracle magazine: It is the 
way Oracle works.

Herald ten Dam
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
     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;
     open c1;
         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');
         -- (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;

SQL> exec p1;

7 row(s) inserted
7 row(s) inserted
3 row(s) inserted
c1 closed

Other related posts: