Oracle SQL Gotcha - Commit in DDL

This happened to one of my fellow developers recently. She had an ALTER statement in the middle of a SQL script that produced an unexpected results when the SQL failed. The problem was mixing DMLs with DDLs in the same script. Read on!

As you probably know, Commits work differently in DMLs and DDLs in Oracle.

• Typically* DML statements (like INSERT, UPDATE, MERGE, DELETE...) needs an explicit COMMIT statement to commit changes to DB.

• DDL statements (like CREATE, CREATE TABLE AS (CTAS), DROP, TRUNCATE etc) don't require a COMMIT statement. DDL actually issues an implicit COMMIT before and after the statement is executed.  This also means, any COMMIT statement after a DDL is not required and has no effect.

So, if you are mixing DMLs and DDLs, be mindful of this. If you have a DML (insert/update...) statement, followed by a DDL statement, you might have inadvertently committed earlier DML. Any later rollbacks won't have any effect.

Here is an example. Test 1 doesn't mix DMLs and DDLs. (There is no DDL immediately after DML). Test 2 on the other hand has a bug in that it has a DDL after the DML statement which results in unpredictable results.

The gotcha here is that, since the commit is done implicitly before even the DDL is executed, this happens even if the DDL failed!! 

Test 1 -- to show the effect of rollback after a DML

-- 1. DDLs 
DROP TABLE test_commit; 
CREATE TABLE test_commit (id int, name VARCHAR2(30));

-- 2. DML
INSERT INTO test_commit VALUES(1, 'Test 1');
-- this shows 1 row
SELECT * FROM test_commit;

-- 3. Rollback below reverses the inserts (no DDLs since last DML)
ROLLBACK;

-- 4. This shows no rows
SELECT * FROM test_commit;

Test 2 - show the effect of an interspersed DDL on commit!

-- 1. DDLs
 DROP TABLE test_commit;
 CREATE TABLE test_commit (id int, name VARCHAR2(30));

-- 2. DML
INSERT INTO test_commit VALUES(1, 'Test 1');
-- This shows 1 row
SELECT * FROM test_commit;

-- 2a. This DDL implicitly commits, so above insert is now committed.
DROP TABLE temp_table2;

-- temp_table2 doesn't really exist - I just used it to show it really doesn't matter, 
-- commit happens implicitly in the DDL

-- 3. This rollback is same as in Example 1, but this has no effect 
ROLLBACK;

-- 4. his shows 1 row; if the rollback above worked, we wouldn't see any rows here. 
SELECT * FROM test_commit; 

-- End of Test; cleanup
DROP TABLE test_commit; 

Notes:
* When Autocommit is off, which is the default in Oracle