oracle-databaseplsqlbulk-collect

Oracle Bulk Collect into Collection using LOOP


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?


Solution

  • 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