Re: bulk collect into ... limit N

From: Nigel Thomas <>
Date: Wed, 1 Jul 2009 08:29:29 +0100
Message-ID: <>


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:

   FETCH... BULK COLLECT LIMIT N    FOR i in 1..cursor%count
       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


Received on Wed Jul 01 2009 - 02:29:29 CDT

Original text of this message