Re: bulk collect into ... limit N
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-lReceived on Wed Jul 01 2009 - 02:29:29 CDT