4 Replies Latest reply on Aug 14, 2014 1:54 PM by rp0428

    Is commit necessary after DML operation on table using simple sql query?

    simyog

      I have confusion about COMMIT command. Is it compulsary to run COMMIT command after DML operation in Sql Developer.

      Because i was done with an UPDATE query..

       

       

      UPDATE TAB1 
      SET TBX_TYP='ZX'
      WHERE TBX_TYP IN(SELECT TBX_TYP
                                        FROM(SELECT DISTINCT TBX_TYP 
                                                     FROM TAB1 ORDER BY 1 )
                                       WHERE ROWNUM=1);
      

      and when i did filtering on columns then i found nothing was updated. Please suggest.

        • 1. Re: Is commit necessary after DML operation on table using simple sql query?
          thatJeffSmith-Oracle

          It's only necessary if you want your transaction work to be permanent. If you don't commit, then the change hasn't really happened - at least not outside your session for others to see.

           

          You have to be careful and keep the following in mind:

          • running a DDL (create, alter, drop) implicitly commits a session
          • exiting tools like SQL*Plus will auto-commit your work
          • SQL Developer has an auto-commit preference (which I am not fond of, personally)

           

          If you change your  mind, you can issue a ROLLBACK - which does exactly what it sounds like.

          • 2. Re: Is commit necessary after DML operation on table using simple sql query?

            I have confusion about COMMIT command. Is it compulsary to run COMMIT command after DML operation in Sql Developer.

            Because i was done with an UPDATE query..

            No other session (or connection) can access UNCOMMITTED data of another session. So if you executed an update from one session/connection and then queried the data from a different session/connection the second session/connection would NOT see the changes made by UPDATE.

             

            It is designed to work like that - dirty reads are NOT allowed by Oracle.

            and when i did filtering on columns then i found nothing was updated.

            What does 'filtering on columns' mean? An update statement doesn't have any columns for you to filter on. So what are you talking about?

            • 3. Re: Is commit necessary after DML operation on table using simple sql query?
              simyog

               

              and when i did filtering on columns then i found nothing was updated.

               


              What does 'filtering on columns' mean? An update statement doesn't have any columns for you to filter on. So what are you talking about?

                

              after updation Instead of checking whole database,  Here i am doing filtering on particular column  to check whether my table is updated or not.

              • 4. Re: Is commit necessary after DML operation on table using simple sql query?

                Sorry - that makes no sense. An update statement doesn't have any columns to 'filter' on.

                 

                Where did these 'columns' come from? Did you run another query? Did you already have a result set open that had data in it?

                 

                What connection was used to do the update? What connection was used to query the data again?