Re: bulk collect into ... limit N

I haven't read the whole thread here, but the SQL%NOTFOUND condition is
raised when the fetch reaches the end of data condition.

Let say you fetch 10 rows at a time using bulk collect, so if you have only
9 rows in the resultset, then the bulk collect returns 9 rows *AND* raises
SQL%NOTFOUND.

So, SQL%NOTFOUND rather means "end of data reached", not "no data at all"...

--
Tanel Poder
http://blog.tanelpoder.com


On Wed, Jul 1, 2009 at 1:05 AM, Tony Adolph <tony.adolph.dba@xxxxxxxxx>wrote:

> 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
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


-- 
Tanel Poder
http://blog.tanelpoder.com

Other related posts: