Create an account

Very important

  • To access the important data of the forums, you must be active in each forum and especially in the leaks and database leaks section, send data and after sending the data and activity, data and important content will be opened and visible for you.
  • You will only see chat messages from people who are at or below your level.
  • More than 500,000 database leaks and millions of account leaks are waiting for you, so access and view with more activity.
  • Many important data are inactive and inaccessible for you, so open them with activity. (This will be done automatically)


Thread Rating:
  • 716 Vote(s) - 3.47 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Is COMMIT required after every EXECUTE IMMEDIATE?

#1
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?
Reply

#2
The only times that you're really forced to commit, other thasn at the end of a business transaction, are:

1. When executing DDL: the DDL execution is wrapped in a pair of implicit commits.
2. After direct path insert: the table cannot be read until the insert is committed.

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).
Reply

#3
Commit is not required after every EXECUTE IMMEDIATE. Certain statements do NOT require a commit; for example, if you truncate a table with TRUNCATE. The truncation is done and there is no need for a commit. no ROLLBACK either. You need to know that COMMIT and ROLLBACK are session attributes. All uncommitted work within the current transaction are committed or rolled back - not just the statement executed by the EXECUTE IMMEDIATE.
Reply



Forum Jump:


Users browsing this thread:
1 Guest(s)

©0Day  2016 - 2023 | All Rights Reserved.  Made with    for the community. Connected through