4 Replies Latest reply on Jan 26, 2018 8:02 PM by turiyag

    PL SQL Auto Commit in SQLDeveloper

    KathyB

      We have turned off the Database>Advanced>autocommit option. 

       

      Which works for items in the worksheet, but when I run a stored procedure it automatically commits.

       

      Where/why is the pl/sql procedures auto committing?

       

      Thanks,

      Kathy

        • 1. Re: PL SQL Auto Commit in SQLDeveloper
          thatJeffSmith-Oracle

          How exactly are you running your stored procedure in SQL Developer? Do you have  COMMIT at the end of your anonymous block?

           

          commit1.png

           

          does your code have any ddl? That will implicitly commit any transactions.

          • 2. Re: PL SQL Auto Commit in SQLDeveloper
            KathyB

            Hi Jeff,

             

            We are executing one of the procedures from a package.

             

            We are just using the green arrow from the toolbar.

             

             

            We do not have any commits in the code.  We do have a rollback command, but it is commented out.

             

            Is there a difference based on how it is executed?

            • 3. Re: PL SQL Auto Commit in SQLDeveloper

              Which works for items in the worksheet, but when I run a stored procedure it automatically commits.

              Ok - then SHOW US output that confirms what you say above.

               

              1. Show us what you execute in a worksheet that 'works'

              2. Show us what code you execute in the stored procedure that 'automatically commits'.

               

              As Jeff said ANY DDL that executes will perform an implicit commit before and after the ddl executes. Without seeing your code we have NO WAY of knowing what your code is doing.

              • 4. Re: PL SQL Auto Commit in SQLDeveloper
                turiyag

                I literally JUST figured this out. Thought I'd share.

                 

                Try this. Select your whole worksheet, run it with Ctrl+Enter without COMMIT at the end, and then use a separate connection to verify that it's not committed. (Or mash ROLLBACK and make sure it goes back)

                 

                Then, run the worksheet as a script with F5. Use your other connection, you'll find that it's committed! Damned button!

                 

                Has nothing to do with PL/SQL, I've just never wanted to run a whole worksheet until I started PL/SQLing