1 Reply Latest reply: Feb 12, 2013 4:07 PM by Gary Graham-Oracle RSS

    SQL Developer auto-commiting when debugging or running stored procedure.

      I have a procedure stored in my database that I've opened using SQL Developer. When I open the procedure (from the Connections tab on the left pane), and execute the procedure using Run or Debug, I find that SQL Developer auto-commits the changes that happen within the procedure.

      I am checking for results using a different session (different program entirely, actually), so I know it's actually a commit and not just uncommited data that I can see.

      My autocommit box is unchecked in Tools > Preferences > Database > Advanced.

      The procedure does NOT commit data when executed in a SQL Developer worksheet (copy and pasted the PL/SQL from the "Run" or "Debug" dialog box).

      The procedure does NOT commit data when run from a program other than SQL Developer.

      The procedure does NOT commit data if I manually alter the code from the "Run" or "Debug" dialog to issue a rollback after the procedure has completed.

      This is SQL Developer using Java Platform 1.6.0_37. I am running Windows 7 Professional 64 bit. The SQL Developer instance is a 32-bit version.

      Any ideas?

      Edited by: 929664 on Feb 11, 2013 5:57 PM
        • 1. Re: SQL Developer auto-commiting when debugging or running stored procedure.
          Gary Graham-Oracle

          The Tools > Preferences > Database > Advanced > Autocommit checkbox only applies to the worksheet, not to execution from the code editor. If you have the log open (main toolbar: View > Log), you will see the following there when you run from the code editor:
          Connecting to the database <name>
          Process exited.
          Disconnecting from the database <name>
          It always opens a new connection and always commits before closing it, so it works as designed.

          If you want to test a procedure and still be able to rollback with auto-commit off, then do something like this in the worksheet...
          execute <proc_name>(<params1>, ...);
          select <whatever to verify the procedure's actions>
          SQL Developer Team