Feuerstein's article on BULK COLLECT and %NOTFOUND

Steven Feuerstien wrote an informative article (http://www.oracle.com/technology/oramag/oracle/08-mar/o28plsql.html) on using BULK COLLECT with LIMIT in a "reasonable" manner. Much of this content is drawn from that article. Basically, the use the LIMIT construct requires a secondary loop to process the smaller "batch". Our DBAs require us to do this kind of "batching" to put less strain on Replication. Smaller COMMITs going across the pipe rather than a 100,000 COMMIT. In otherwords, "commit every x number of rows." [Search engine fodder]

PROCEDURE process_all_rows (limit_in IN PLS_INTEGER DEFAULT 100)
IS
   CURSOR employees_cur
   IS
       SELECT * FROM employees;

   TYPE employees_aat IS TABLE OF employees_cur%ROWTYPE
       INDEX BY PLS_INTEGER;

   l_employees employees_aat;
BEGIN  
   OPEN employees_cur;
   LOOP
       FETCH employees_cur
           BULK COLLECT INTO l_employees LIMIT limit_in;

       FOR indx IN 1 .. l_employees.COUNT LOOP
           update_compensation (l_employees(indx));
       END LOOP;

       COMMIT; -- Commit batch of size LIMIT 

       EXIT WHEN l_employees.COUNT < limit_in;

   END LOOP;

   CLOSE employees_cur;
END process_all_rows;

To summarize his second article, don't use %NOTFOUND with BULK COLLECT and LIMIT. Use .COUNT. Secondary Example

set serveroutput on;
clear;

declare
  CURSOR contracts_cur
   IS
       SELECT * FROM contract where rownum <= 50;

   TYPE t_contract IS TABLE OF contracts_cur%ROWTYPE
       INDEX BY PLS_INTEGER;

   l_contract t_contract;
   
BEGIN  
   OPEN contracts_cur;
   LOOP
       FETCH contracts_cur
           BULK COLLECT INTO l_contract LIMIT 10;

       FOR indx IN 1 .. l_contract.COUNT LOOP
        common_func.display_output(indx||' : '||l_contract(indx).contract_id);
       END LOOP;

       EXIT WHEN l_contract.COUNT = 0;

   END LOOP;

   CLOSE contracts_cur;
   
   EXCEPTION 
    WHEN OTHERS THEN 
       common_func.display_output(SQLERRM);
   
END;