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

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

    932667
    932667 Feb 11, 2013 10:58 PM
      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 3.2.20.09.87 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
          Gary Graham-Oracle Feb 12, 2013 10:07 PM (in response to 932667)
          Hi,

          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>
rollback;
          Regards,
          Gary
          SQL Developer Team