Power to Build

Home » CodeProject » Oracle SQL Gotcha – Commit in DDL

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


1 Comment

  1. […] for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it’s automatically committed […]

Comments, please?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: