Oracle Gotcha For The SQL Server Developer – DDL’s COMMIT Transactions Implicitly!

If you are primarily a SQL Server developer and you are moving to Oracle, this is one of the nastiest surprises in store for you. You are so used to rolling back everything including DDL in SQL Server transactions but in Oracle, DDL inside transactions act as COMMIT. Fear not though, there are a ton of other cool things in PL/SQL that will make you forget this one down-side. TSQL has years to catch-up to PL/SQL (IMHO, even as of 1/1/2017). In fact, that is one facet of the SQL Server product that has remained mostly unchanged since its honeymoon days with Sybase and it feels like it is the bastard child within the SQL Server product family, that got completely neglected.

If you did not know, another surprise for the SQL Server developer (not necessarily a bad thing) is that by default all Oracle statements are implicitly inside a transaction. You have to explicitly COMMIT or ROLLBACK. ROLLBACK will be assumed when session ends. I would argue that this is a good thing.

Additionally and to reiterate:

  • Your DDL (Data Definition Language) statements inside PL/SQL have to be dynamic SQL run with EXECUTE IMMEDIATE. i.e., it cannot be regular parsed code.
  • Any DDL statement inside PL/SQL acts also as an implicit COMMIT for the transaction in progress in addition to doing the work of the DDL statement

Let us consider an example in Oracle to illustrate

  • Create two tables – TEST_1 and TEST_2
CREATE TABLE TEST_1
(
   COL1      NUMBER(5)
);

CREATE TABLE ususaja.TEST_2
(
   COL1      NUMBER(5)
);
  • Test inserting into TEST_1
BEGIN
   INSERT INTO TEST_1(col1) VALUES (1);

   COMMIT;
END;
  • Get the count of rows in TEST_1 – No surprises there, we get a return count of 1 for the 1 row we inserted
SELECT COUNT(1) FROM TEST_1;

Returns value: 1
  • Clean-up TEST_1 table
DELETE FROM TEST_1;

COMMIT;
  • Do the same insert to TEST_1 but instead of COMMIT, run a TRUNCATE against a totally different table TEST_2
BEGIN

   INSERT INTO TEST_1(col1) VALUES (1);

   --We are truncating TEST_2 not TEST_1
   EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_2';  

END;
  • Get the count of rows in TEST_1
SELECT COUNT(1) FROM TEST_1; 

Returns value: 1

This still returns 1 for 1 row in the table proving that the EXECUTE IMMEDIATE caused the transaction to COMMIT the INSERT. Please note that the TRUNCATE was against a totally different table TEST_2. Since TRUNCATE TABLE is a DDL, it committed the transaction.

  • Clean-up TEST_1 table
DELETE FROM TEST_1;

COMMIT;
  • Let us test a rollback after a DDL
BEGIN

   INSERT INTO TEST_1(col1) VALUES (1);

   --We are truncating TEST_2 not TEST_1
   EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_2';

   INSERT INTO TEST_1(col1) VALUES (2);

   ROLLBACK;

END;

SELECT COUNT(1) FROM TEST_1;

Returns value: 1

This still returns 1 for 1 row in the table that was committed by the DDL. The INSERT done after the DDL was rolled back.

When mixing DML and DDL in your code, please pay attention as the ROLLBACK may not rollback what you intended to rollback.

As you know Oracle folks consider SQL Server as a totally different animal (and vice versa) where DDL is transactional and transactions are implicit unless you explicitly change that behavior.

Leave a comment