Power to Build

Home » CodeProject » Quick Note: ORACLE SQL – COMMIT is not needed after DDL

Quick Note: ORACLE SQL – COMMIT is not needed after DDL

I saw the below type of SQL statements in several scripts at work today, so I decided post this as a reminder.

SQL>
SQL> DROP TABLE TMP_SUPPL_ID;

Table dropped.

SQL>
SQL> COMMIT;

Commit complete.


The author of this script dropped a table, then sincerely commits the transaction. A DDL, like DROP TABLE statement above, does an implicit commit, so a commit is not required after DDL. This is why we cannot reverse a DDL operation with a rollback. The example above is an Oracle script, but I believe this is applicable to all databases.

The commit in this case is not an error, but shows the lack of understanding about the DDL. In an earlier post, I mentioned about mixing DMLs and DDLs. That will have a more dramatic impact! So, be aware that DDLs commit after themselves!


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: