SQL & PL/SQL

DB version : 11.2

I gather that gurus here despise COMMITs inside loops. Due to budget constraints our java colleagues write PL/SQL code here. Below is an excerpt from a stored procedure which does some archiving stuff.


Question on COMMIT
=====================
I know COMMITs inside loops are bad, but I just want to know if the way they have implemented COMMIT frequency is correct (using iteration count). Their intention was to commit every 200,000 records.


Question on EXCEPTION
======================
On DMLs like this , isn't it better to have exception block within the loop ?

 
 
select po.txn_id, po.po_id BULK COLLECT into v_txn_id, v_po_id 
from purchase_orders po inner join txn_pkt_common tc 
on tc.txn_id = po.txn_id
and tc.created_date < po.last_mod_date;


v_rec_count := 0;


IF v_txn_id IS NOT NULL AND v_txn_id.COUNT > 0 THEN


	FOR i IN v_txn_id.FIRST .. v_txn_id.LAST LOOP

		v_rec_count := v_rec_count + 1;

		INSERT INTO purchase_orders_archive SELECT * FROM purchase_orders WHERE po_id = v_po_id(i);

		DELETE FROM purchase_orders WHERE po_id = v_po_id(i);


		IF v_rec_count >= 200000 THEN
		COMMIT;
		v_rec_count := 0;
		END IF;

	 END LOOP;
	COMMIT; 

end if;