9 Replies Latest reply: Dec 6, 2012 7:17 AM by Hoek RSS

    Is Merge Statement needs commit ?

    944832
      Hi Team,

      I used merge statement, Commit need or not and Rollback will work please suggest?

      In existed procedures written by others, i have not found any commit statement.

      Regards,
        • 1. Re: Is Merge Statement needs commit ?
          905562
          It falls into the same category as all other DML.

          So yes, it's a transaction like any other.
          • 2. Re: Is Merge Statement needs commit ?
            Marwim
            Hello,

            MERGE needs a COMMIT just like any DML.
            i have not found any commit statement.
            The commit should be done by the caller. Maybe you should search in the GUI/frontend.

            Regards
            Marcus
            • 3. Re: Is Merge Statement needs commit ?
              Paul  Horth
              941829 wrote:
              Hi Team,

              I used merge statement, Commit need or not and Rollback will work please suggest?

              In existed procedures written by others, i have not found any commit statement.

              Regards,
              Merge is just like any other DML and will require a commit or rollback as any other DML statement at the end of the transaction.

              Note: at the end of the transaction. That is why other procedures don't have a commit, the commit or rollback should generally
              be done by the controlling application, which knows the transactional context.
              • 4. Re: Is Merge Statement needs commit ?
                Rahul_India
                941829 wrote:
                Hi Team,

                I used merge statement, Commit need or not and Rollback will work please suggest?

                In existed procedures written by others, i have not found any commit statement.

                Regards,
                yes ..you need to commit MERGE STATEMENT.Its a DML statement like insert.
                • 5. Re: Is Merge Statement needs commit ?
                  BluShadow
                  941829 wrote:
                  Hi Team,

                  I used merge statement, Commit need or not and Rollback will work please suggest?
                  It depends. With a MERGE statement you will need to commit it if you go on to try and do further DML against the same table otherwise it throws an error (try it yourself and see - *1)
                  Typically though, if that's not the case, you would only commit when you reach the end of the logical business transaction.

                  Yes, you can use rollback if you need.

                  Edited by: BluShadow on 06-Dec-2012 13:04
                  *1 just trying to remember the situation where a commit is required after a merge... can't for the life of me remember what the error was it was throwing without committing..... hmmmm.... goes off to search
                  • 6. Re: Is Merge Statement needs commit ?
                    Hoek
                    Yes, you would need to commit or rollback.
                    It could very well be that the existing procedures have no commit or rollback in them, because the creators of those procedures decided to let the callers / invokers of the procedures issue a commit or rollback.

                    disclaimer
                    All of this assuming that autocommit or other weird, automagical stuff is not being used to control transactions.
                    • 7. Re: Is Merge Statement needs commit ?
                      944832
                      Thanks alot Friends.

                      I have clear my doubt.
                      • 8. Re: Is Merge Statement needs commit ?
                        Hoek
                        Edited by: BluShadow on 06-Dec-2012 13:04
                        *1 just trying to remember the situation where a commit is required after a merge... can't for the life of me remember what the error was it was throwing without committing..... hmmmm.... goes off to search
                        Wild guess:
                        Was there perhaps direct path loading involved?
                        • 9. Re: Is Merge Statement needs commit ?
                          BluShadow
                          Hoek wrote:
                          Edited by: BluShadow on 06-Dec-2012 13:04
                          *1 just trying to remember the situation where a commit is required after a merge... can't for the life of me remember what the error was it was throwing without committing..... hmmmm.... goes off to search
                          Wild guess:
                          Was there perhaps direct path loading involved?
                          nope, that wasn't it...
                          I've found the code with commits in there after some of the merge statements... just forgot (ahem!) to put comments in the code to explain why. :)
                          I'm sure it was to do with subsequent merge statements.... maybe it'll come back to me.