http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html
In this page they have mentioned that:
When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes to decide whether to terminate your loop and data processing.
He mentions that, to make sure that our query processes all the rows, we should
NOT USE:
EXIT WHEN
cursor%NOTFOUND;
and we SHOULD USE:
EXIT WHEN
collectionvariable.count=0;
What is the reason?
Article states clearly that when using cur%NOTFOUND
it will skip processing some records.
Please check self-contained example:
DECLARE
TYPE item_tab IS TABLE OF PLS_INTEGER;
l_item item_tab;
CURSOR get_item_value IS
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 25;
BEGIN
OPEN get_item_value;
LOOP
FETCH get_item_value BULK COLLECT INTO l_item LIMIT 10; -- 10 10 5
EXIT WHEN get_item_value%NOTFOUND; -- FALSE FALSE TRUE
DBMS_OUTPUT.put_line(l_item.COUNT);
END LOOP;
CLOSE get_item_value;
END;
Output:
10
10
-- 5 record left
And second version:
DECLARE
TYPE item_tab IS TABLE OF PLS_INTEGER;
l_item item_tab;
CURSOR get_item_value IS
SELECT LEVEL
FROM dual
CONNECT BY LEVEL <= 25;
BEGIN
OPEN get_item_value;
LOOP
FETCH get_item_value BULK COLLECT INTO l_item LIMIT 10; -- 10 10 5 0
EXIT WHEN l_item.COUNT = 0; -- F F F T
DBMS_OUTPUT.put_line(l_item.COUNT);
END LOOP;
CLOSE get_item_value;
END;
Output:
10
10
5