Wrap Oracle Schema Update in a Transaction

Question

I have a program that periodically updates its database schema. Sometimes one of the DDL statements can fail, and if that happens, I want to undo all the changes. I transfer the update to the transaction as follows:

BEGIN TRAN; CREATE TABLE A (PKey int NOT NULL IDENTITY, NewFieldKey int NULL, CONSTRAINT PK_A PRIMARY KEY (PKey)); CREATE INDEX A_2 ON A (NewFieldKey); CREATE TABLE B (PKey int NOT NULL IDENTITY, CONSTRAINT PK_B PRIMARY KEY (PKey)); ALTER TABLE A ADD CONSTRAINT FK_B_A FOREIGN KEY (NewFieldKey) REFERENCES B (PKey); COMMIT TRAN; 

As we execute, if one of the statements fails, I do ROLLBACK instead of COMMIT. This works fine on SQL Server, but does not have the desired effect for Oracle. It seems that Oracle does an implicit COMMIT after each DDL statement:

• http://www.orafaq.com/wiki/SQL_FAQ#What_are_the_difference_between_DDL.2C_DML_and_DCL_commands.3F
• http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html#transactions

Is it possible to disable this implicit commit?

Answer 1

You cannot disable this. It’s easy enough to work by creating your own scripts to discard tables if they already exist, etc.

You can look using the FLASHBACK database, I believe that you can do this at the schema / object level, but check the documents to confirm this. For this you need to be on 10G.