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 Aug 12, 2014 12:39 PM

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 Aug 12, 2014 1:19 PM (in response to simyog)
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.
Like Show 0 Likes(0)

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

rp0428 Aug 12, 2014 2:12 PM (in response to 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..

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?
Like Show 0 Likes(0)

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

simyog Aug 14, 2014 9:17 AM (in response to rp0428)

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.
Like Show 0 Likes(0)

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

rp0428 Aug 14, 2014 1:54 PM (in response to simyog)

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?
Like Show 0 Likes(0)

Actions

Go to original post