Re: bulk collect into ... limit N
Date: Wed, 1 Jul 2009 08:29:29 +0100
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).