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;
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;
