2 Replies Latest reply on Mar 30, 2014 3:24 PM by rp0428

    commit or roll back automatically

    ANNEC


      In oracle sql developer if I manually click run next to a stored procedure, then it will generates code like:

       

      Begin

      execute mysproc;

      End;

       

      My question is : will above command will automatically commit all transactions in the stored procedure even I don't have a commit statement below the execute my sproc statement?

      and will it also rollback all the transactions in the stored procedure if any errors happened  -even I don't explicitly have a roll back statement?

       

      Thanks

        • 1. Re: commit or roll back automatically
          Gary Graham-Oracle

          I assume this is a follow-up to your earlier post:  rollback after insert fail

           

          It depends.  SQL Developer connects to the database and processes SQL statements using JDBC.  In general, JDBC connections default to using auto-commit mode:  Using Transactions (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Bas…

           

          However, the default user setting in SQL Developer is exactly the opposite, more common use case where auto-commit is unchecked:

          Tools > Preferences > Database > Advanced > Autocommit

           

          A discussion of the equivalent for SQL*Plus (even though you did not ask) is here: Autocommit

           

          And keep in mind DDL (data definition language) differs from DML (data manipulation language): it always assumes auto-commit, although there can be some subtleties: Auto commit DDL: Difference between SQL developer and SQL*Plus?

           

          Regards,

          Gary

          • 2. Re: commit or roll back automatically
            rp0428
            In oracle sql developer if I manually click run next to a stored procedure, then it will generates code like:

             

            Begin

            execute mysproc;

            End;

             

            My question is : will above command will automatically commit all transactions in the stored procedure even I don't have a commit statement below the execute my sproc statement?

            and will it also rollback all the transactions in the stored procedure if any errors happened  -even I don't explicitly have a roll back statement?

            How could anyone but you possibly know?

             

            You didn't even bother to tell anyone what database and what DB version you are even using. Your question is like asking us if your car has a radio - we don't know since we have know way of looking into your car to see.

             

            What result did you get when you tested it to see what would happen?

             

            Don't be afraid of breadking your database by actually TRYING things.

             

            The best way to learn is BY DOING - not by reading or just asking questions.

             

            Conduct your own tests.

             

            1. Create a procedure

            2. add a transaction to the procedure

            3. do your 'manually click run next to' the procedure

            4. sit back and WATCH

             

            What happened? THAT is your answer. You don't need anyone elses help to answer your question.

            1 person found this helpful