DELETE RECORDS USING BULK COLLECT - ORACLE

Below is the procedure to delete records from table1 using bulk collect method. In the below example the limit is specified as 100, 000 records.


Pl/Sql :

SET SERVEROUTPUT ON
DECLARE
TYPE t_bulk_del IS TABLE OF TABLE2%ROWTYPE;
l_tab t_bulk_del;
CURSOR bulk_del IS
SELECT RECID FROM TABLE2;
BEGIN
OPEN bulk_del;
LOOP
FETCH bulk_del
BULK COLLECT INTO l_tab LIMIT 100000;
forall vloop in 1 .. l_tab.count
delete from TABLE1 where recid = l_tab(vloop);
commit;
EXIT WHEN l_tab.count = 0;
DBMS_OUTPUT.put_line(l_tab.count || ' rows');
END LOOP;
CLOSE bulk_del;
END;
/