How to delete the records from a table by using bulk collect and forall ?

Hi, In this article We are going to know how to delete the records by using bulk binds(Bulk collect & Forall).


CREATE OR REPLACE PACKAGE XX_BULK_RECORDS_DELETE_PKG
IS

PROCEDURE XX_BULK_DELETE;

END XX_BULK_RECORDS_DELETE_PKG;

/
SHO ERRORS
/


CREATE OR REPLACE PACKAGE BODY XX_BULK_RECORDS_DELETE_PKG
IS

PROCEDURE XXPC_BULK_DELETE
IS
-- +====================================================================+
-- | lcu_mtl_trxns cursor is used to get the transactions from
-- | mtl_material_transactions which are not having consted_flag as 'Y'
-- +====================================================================+
CURSOR  lcu_mtl_trxns
IS
SELECT  transaction_id
FROM    mtl_material_transactions_bkup
WHERE   costed_flag <> 'Y'
AND     organization_id = 889;

-- +====================================================================+
-- |Declaring Local Variables.                                       
-- +====================================================================+
TYPE bulk_rec IS TABLE OF lcu_mtl_trxns%rowtype;
bulk_tab    bulk_rec;
bulk_errors NUMBER;
dml_errors  EXCEPTION;
PRAGMA exception_init(dml_errors,-24381);

BEGIN

OPEN lcu_mtl_trxns;
LOOP
FETCH lcu_mtl_trxns BULK COLLECT INTO bulk_tab LIMIT 100;

FORALL indx IN bulk_tab.FIRST .. bulk_tab.LAST SAVE EXCEPTIONS
DELETE FROM mtl_material_transactions_bkup
WHERE transaction_id = bulk_tab(indx).transaction_id;
EXIT WHEN lcu_mtl_trxns%notfound;

END LOOP;
CLOSE lcu_mtl_trxns;

COMMIT;

EXCEPTION
WHEN dml_errors THEN
bulk_errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('number of statements failed are '||bulk_errors);
FOR ind IN 1..bulk_errors LOOP
dbms_output.put_line('error #'||ind|| ' is occured during '|| 'iterations #' ||sql%bulk_exceptions(ind).error_index);
dbms_output.put_line('error  message is ' || SQLERRM(-sql%bulk_exceptions(ind).error_code));

end loop;

WHEN OTHERS THEN
dbms_output.put_line('Err is: '||SQLCODE ||' , '||SQLERRM);
END XX_BULK_DELETE;

END XX_BULK_RECORDS_DELETE_PKG;
/
SHO ERRORS
/
EXEC XX_BULK_RECORDS_DELETE_PKG.XXPC_BULK_DELETE;

select * from mtl_material_transactions_bkup;