Oracle Bulk Collect into Collection using LOOP

Question

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?

It is explained in the article which you are refer to. See Code Listing 2 and its explanations. — Dmitriy

Lukasz Szozda Lukasz Szozda · Accepted Answer · 2017-08-21T13:09:08

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

Oracle Bulk Collect into Collection using LOOP

1 Answers