0

In Oracle, I am invoking the following querys (not from sqlplus) as part of an installation script:

ALTER TABLE MYTABLE DISABLE CONSTRAINT PFTATTRS_ATTR_FK;
INSERT INTO MYTABLE (PTF_ID, ATTR_ID) VALUES (1, 5);
ALTER TABLE MYTABLE ENABLE CONSTRAINT PFTATTRS_ATTR_FK;

As you see I'm interleaving DMLs (that require COMMIT) with DDLs (that are auto-committed). My doubt is: do I need to commit the DMLs before every DDL, or is it safe to do one big commit at the end of my script?

crusaderky
  • 2,552
  • 3
  • 20
  • 28
  • It is still cleaner to commit before the ddl and it also makes the intent of the code considerably more obvious. The cost of that additional commit is small. Generally an additional roundtrip and even that can be avoided. – steve Jan 18 '14 at 03:33

1 Answers1

5

See here.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:7072180788422

https://community.oracle.com/message/10310617

Each DDL statement will commit all previous uncommitted DML statements.

peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • Yes, DDL statements generally issue 2 commits: one before they start, and one after they finish (unless they error, in which case they don't issue the 2nd commit). – Jeffrey Kemp Jan 17 '14 at 04:42
  • Jeffrey the second part is not entirely correct, there are also ddl/dcl that do not do a second commit without failing. – steve Jan 18 '14 at 03:31