I have multiple EXECUTE IMMEDIATE commands within one oracle procedure.
EXECUTE IMMEDIATE 'DELETE FROM tbl1';
EXECUTE IMMEDIATE 'INSERT INTO tbl1...';
COMMIT;
EXECUTE IMMEDIATE 'DELETE FROM tbl3';
EXECUTE IMMEDIATE 'INSERT INTO tbl3 ...';
COMMIT;
EXECUTE IMMEDIATE 'DELETE FROM tbl4';
EXECUTE IMMEDIATE 'INSERT INTO tbl4 ...';
COMMIT;
Do I need all of these COMMIT, or just at the end of the procedure?
The only times that you're really forced to commit, other thasn at the end of a business transaction, are:
As horsey comments, the correct point to commit at is when the business transaction is complete. Otherwise, you need to be writing yourself some code to detect and fix partially completed and commited transactions that have left the database is a logically inconsistent state (eg. An INVOICE record exists without any INVOICE_DETAIL records).