DDL Statement v/s Commit Statement

Some important facts about DDL and Commit statements

Whenever a DDL statement is executed Oracle internally does the following processing:

1. Parsing of the DDL statement (Checks for the Syntax of the statement)
2. Commit (If parsing is successful)
3. Execute the DDL
4. Commit

It means Oracle issues an implicit commit BEFORE (After Successful parsing) and AFTER execution of any data definition language (DDL, Like CREATE TABLE…, ALTER TABLE…, DROP TABLE…, ANALYZE TABLE…, CREATE PROCEDURE/SEQUENCE etc. etc.) statement.

So, even if your DDL statement parse successfully but fails during execution, you cannot roll back the previous transactions because the server issued a commit.

Let us check this by an example.

SQL> CREATE TABLE DDL_COMMIT(test_value VARCHAR2(100));

Table created.

SQL> INSERT INTO DDL_COMMIT(test_value) VALUES('INSERT BEFORE DDL STATEMENT');

1 row created.

--This DDL statement is syntactically correct hence will be parsed successfully and will issue an internal commit. 

SQL> DROP TABLE XYZ;

DROP TABLE XYZ

           *

ERROR at line 1:

ORA-00942: table or view does not exist

--This Rollback statement can’t rollback the last INSERT as it is already committed to database.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT *

  2    FROM DDL_COMMIT;

TEST_VALUE

---------------------------

INSERT BEFORE DDL STATEMENT

Now let us check what happens to the transactions when a syntactically incorrect DDL is issued.

SQL> INSERT INTO DDL_COMMIT(test_value) VALUES('INSERT BEFORE Syntactically In-Correct DDL STATEMENT');

1 row created.

SQL> SELECT *

  2    FROM DDL_COMMIT;

TEST_VALUE

---------------------------------------------------

INSERT BEFORE DDL STATEMENT

INSERT BEFORE Syntactically In-Correct DDL STATEMENT 

--This DDL statement is syntactically in-correct hence will fail during parsing itself and internal commit will not be issued. 

SQL> DROP TABLE XYZ ABC;

DROP TABLE XYZ ABC

               *

ERROR at line 1:

ORA-00933: SQL command not properly ended

--This Rollback statement will rollback the last INSERT

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT *

  2    FROM DDL_COMMIT;

TEST_VALUE

---------------------------

INSERT BEFORE DDL STATEMENT

Since DDL “DROP TABLE XYZ” was executed after the first record inserted into table. It got committed. You can see that even DDL command (DROP TABLE XYZ;) fails during execution the data gets committed into database.

However the 2^nd DDL statement “DROP TABLE XYZ ABC” which is syntactically incorrect fails at the time of parsing itself hence the 2^nd record didn’t get automatically committed to database.  

So whenever you use any DDL statement in any of your program using DYNAMIC SQL (Using EXCECUTE IMMEDIATE) take care of the Transaction commit/rollback before that DDL statement.

If it is really necessary to use a DDL in PL/SQL program, it is advisable to use a separate procedure with Autonomous Transaction for that DDL so that it does not commit the previous transactions.