This discussion is archived
1 Reply Latest reply: Feb 12, 2013 2:07 PM by Gary Graham RSS

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

932667 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points