Bulk Collect for Delete

CREATE OR REPLACE PROCEDURE BLK_CLCT_DEL
IS
L_LIMIT PLS_INTEGER := 20000;

CURSOR C1
IS
SELECT row_id
FROM s_invoice_item
WHERE person_id IN
(SELECT b.row_id
FROM disalloc A, S_CONTACT B
WHERE A.PD_NEW_EYEE_ID = B.PERSON_UID
AND B.CON_CD = ‘Member’
AND A.PD_DT_MODIFY BETWEEN ’01-JAN-2010′
AND ’31-MAY-2010′);

TYPE temp_mem IS TABLE OF C1%ROWTYPE
INDEX BY PLS_INTEGER;

tem_mp temp_mem;
BEGIN
OPEN C1;

LOOP
FETCH C1
BULK COLLECT INTO tem_mp
LIMIT L_LIMIT;

EXIT WHEN tem_mp.COUNT = 0;

FORALL indx IN 1 .. tem_mp.COUNT
DELETE FROM s_invoice_item
WHERE row_id = tem_mp (indx).row_id AND row_id LIKE ‘%-%-%’;

DBMS_OUTPUT.put_line (TO_CHAR (SQL%ROWCOUNT) || ‘ rows deleted’);
COMMIT;
END LOOP;

CLOSE C1;
END;