6 Replies Latest reply: Feb 15, 2013 2:09 PM by user346369 RSS

    Oracle's Automatic Commit bites.

    user346369
      I got bitten by the Automatic Commit that Oracle performs before a DDL operation. First time for me after many years writing PL/SQL. The Perfect Storm of mistakes brought it about...

      I'm writing a PL/SQL process to weed out old and bad data from our system. So the process steps through a number of tables, looking for all records with a specific condition and key ID value. Then, it cycles through all tables where related data may live, and deletes rows. I wrote a dynamic "Execute Immediate" process to first lock, and then delete rows. That part is working ( unfortunately, this time).

      In addition, I was using Commit and Rollback to either keep or undo my changes. The SQL DELETE is actually running, only I planned to code a Rollback at the end to undo my changes. I realized I had a problem when a subsequent test run found no more data to delete -- the Rollback had not occurred.

      In fact, Oracle had AUTOMATICALLY committed my changes!!! Then I realized: In my testing cycle, I change my package code, then use:
      CREATE OR REPLACE PACKAGE....
      This DDL command causes the Auto-Commit to occur.

      The OTHER mistake that brought this about is that within my code, I was expecting multiple rows with the same key ID value, so tried to code the commit or rollback ONLY when the ID value changed. Unfortunately the Three-Value logic problem with Null values came into play, and I nested the code to keep the last ID value inside a condition that was NEVER TRUE (due to my being tripped up by the first-time-in and null condition issue.)

      So I have fixed the problem by ensuring the ID value is always unique (using a Select Max()... Group By Key_ID), and by coding the commit or rollback at the end of the process for EACH ID, rather than before a new ID value is fetched.


      It is curious, then, that if I code a procedure within the package with
      Pragma Autonomous_Transaction;
      followed by an Insert into Log_Table...
      that If I leave that procedure without a commit or rollback, Oracle specifically warns me:
      ORA-06519: active autonomous transaction detected and rolled back
      Cause: Before returning from an autonomous PL/SQL block, all autonomous transactions started within the block must be completed (either committed or rolled back). If not, the active autonomous transaction is implicitly rolled back and this error is raised.

      It would sure be nice if Oracle had a similar warning/error/rollback in place before the DDL Auto Commit.
      I suspect this Automatic Commit before DDL was implemented very early in Oracle's software history, while the Autonomous_Transaction and ORA-06519 was added in much later, after the software became more sophisticated.

      ...or is there a feature/switch that I can set that I don't know about? (It wouldn't be the first time.)