2 Replies Latest reply: Mar 28, 2014 10:01 PM by rp0428 RSS

    rollback after insert fail

    ANNEC

      I have a stored procedure which has a cursor  for loop, and in the for loop there is  an insert statement, I add a commit after it, and also an exception block when others then roll back.

      If there are no errors when inserting, the stored procedure runs fine, but if there is an error, the stored procedure just hang.  I see in the log window, it connects the database, but not disconnect it. If I remove the rollback in the exception handling block, the stored procedure runs fine.

       

      why is that?

       

      Thanks,

        • 1. Re: rollback after insert fail
          Gary Graham-Oracle

          Hi,

           

          If you are in fact using the SQL Developer tool, make sure Tools > Preferences > Database > Advanced > Autocommit is unchecked.

           

          If it was already unchecked, then post your PL/SQL code here.  If no one comments on this forum, then a better forum for such questions is

          SQL and PL/SQL

           

          Regards,

          Gary

          SQL Developer Team

          • 2. Re: rollback after insert fail
            rp0428
            I have a stored procedure which has a cursor  for loop, and in the for loop there is  an insert statement, I add a commit after it, and also an exception block when others then roll back.

            If there are no errors when inserting, the stored procedure runs fine, but if there is an error, the stored procedure just hang.  I see in the log window, it connects the database, but not disconnect it. If I remove the rollback in the exception handling block, the stored procedure runs fine.

             

            why is that?

            Reply posted in SQL and PL/SQL forum where thread belongs.

            Do you really expect anyone to be able to debug code that they can't see?

             

            Please read the FAQ (top of the page) for how to ask a question in the forums.

             

            At a minimum you need to provide:

            1. your full 4 digit Oracle version - currently missing

            2. a description of the problem - ok

            3. the code/query that you are asking about

             

            Based on your description you could be using slow-by-slow (row by row) DML. If the code you post shows that you are be prepared for people to question WHY you are doing that., That is usually an indication of the wrong solution for whatever problem the code is supposed to be solving.

             

            Also expect several comments if that COMMIT you mention is INSIDE the loop instead of after it.

             

            Once you post the code we may be able to help.