Re: bulk collect into ... limit N

From: Nigel Thomas <nigel.cl.thomas_at_googlemail.com>
Date: Wed, 1 Jul 2009 08:29:29 +0100
Message-ID: <53258cd50907010029x278be7b0udf0aa30db31775f3_at_mail.gmail.com>



Tony

The best way to think about %NOTFOUND is to say that if %NOTFOUND is TRUE, then there is no need to do another fetch. That is, %NOTFOUND is really saying "the cursor has detected end of fetch - there are no more rows to be returned from the database".

So when your bulk collect LIMIT N retrieves fewer than N rows, there is no need for you to fetch again. The cursor variables tell you this, but they also tells you how many rows were actually fetched, and your PL/SQL needs to process them.

So using %NOTFOUND after your processing is a perfectly workable approach; use a format like:

LOOP
   FETCH... BULK COLLECT LIMIT N    FOR i in 1..cursor%count
   LOOP
       process the rows
   END LOOP    EXIT WHEN cursor%notfound;
END LOOP; Of course, if %NOTFOUND=TRUE then you know that %count < N. And if you reduce to N=1 (the single row fetch case, with no bulk collect), then %NOTFOUND implies that %count = 0 (and vice versa).

Regards Nigel

>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 01 2009 - 02:29:29 CDT

Original text of this message