-- The bulk record processing performance using BULK COLLECT can be
-- split into required chunks by using LIMIT.  

-- 1. The PL/SQL code shown below does not use LIMIT.

SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
 CURSOR c_hcnt IS
  SELECT level ref_idx FROM DUAL CONNECT BY level < 1000001;

 TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 v_bulk      t_tab;
 v_copy      t_tab;

 idx         SIMPLE_INTEGER :=0;
 v_cnt       SIMPLE_INTEGER :=0;
BEGIN
 OPEN c_hcnt;

 LOOP
  FETCH c_hcnt BULK COLLECT INTO v_bulk;

  v_cnt := v_cnt +1;
  idx := idx + v_bulk.COUNT;

  EXIT WHEN c_hcnt%NOTFOUND;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(' Bulk Processing Count   => '||v_cnt);
 DBMS_OUTPUT.PUT_LINE(' Records Processed Count => '||idx);
 
 CLOSE c_hcnt;
END;
/



-- 2. PL/SQL code using a LIMIT of 5000.

-- The processing count is 1000000/5000 = 200.  The performance is 
-- slightly improved with use of LIMIT.

DECLARE
 CURSOR c_hcnt IS
  SELECT level ref_idx FROM DUAL CONNECT BY level < 1000001;

 TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 v_bulk      t_tab;
 v_copy      t_tab;

 idx         SIMPLE_INTEGER :=0;
 v_cnt       SIMPLE_INTEGER :=0;
BEGIN
 OPEN c_hcnt;

 LOOP
  FETCH c_hcnt BULK COLLECT INTO v_bulk LIMIT 5000;

  idx := idx + v_bulk.COUNT;

  EXIT WHEN c_hcnt%NOTFOUND;
  v_cnt := v_cnt +1;
 END LOOP;
 DBMS_OUTPUT.PUT_LINE(' Bulk Processing Count   => '||v_cnt);
 DBMS_OUTPUT.PUT_LINE(' Records Processed Count => '||idx);
 
 CLOSE c_hcnt;
END;
/



-- The above EXIT option can result in NO_DATA_FOUND exception when the cursor 
-- has no data. To avoid the exception, the code needs to be modified as shown
-- below.  The demo cursor (c_hcnt) shown in the example below has data, in 
-- realtime scenario no data situation can occur in a cursor. The bulk 
-- processing count is 1000000/500 = 2000.

DECLARE
 CURSOR c_hcnt IS
  SELECT level ref_idx FROM DUAL CONNECT BY level < 1000001;

 TYPE t_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 v_bulk      t_tab;

 v_idx       SIMPLE_INTEGER :=0;
 v_cnt       SIMPLE_INTEGER :=0;
BEGIN
 OPEN c_hcnt;

 LOOP
  FETCH c_hcnt BULK COLLECT INTO v_bulk LIMIT 500;

  EXIT WHEN v_bulk.COUNT = 0;

  v_cnt := v_cnt +1;

  FOR idx IN 1..v_bulk.COUNT 
  LOOP
    v_idx := v_idx + 1;
  END LOOP;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE(' Bulk Processing Count   => '||v_cnt);
 DBMS_OUTPUT.PUT_LINE(' Records Processed Count => '||v_idx);
 
 CLOSE c_hcnt;
END;
/