Execute 3 Commit 1

I have executed three SQL statements A, B & C Now I want to commit only statement B only. Is it possible if possible how I can?

You need to user PRAGMA AUTONOMOUS TRANSACTION for the Purpose.

3 points.
[B]-> The Need[/B]

Now, why would anyone want to do that? Execute 3 SQLs and then commit only one? Doesn't really make practical sense(If SQLs are designed right)

[B]-> Simple Solution[/B]

Put the SQL you need to commit inside a BLOCK (By using BEGIN and END) and commit only that block. As simple as that!

[B]->PRAGMA AUTONOMOUS TRANSACTION[/B]

Well- this simply means the transaction block gets executed inependently - doesn't ensure commit unless specified within the BEGIN - END block.

[B]debasisdas[/B], please elaborate on how you intend to use "PRAGMA AUTONOMOUS TRANSACTION" for this case?

For tose of you who wants to know what "PRAGMA AUTONOMOUS TRANSACTION" is , follow the below link.

[URL="http://www.unix.org.ua/orelly/oracle/guide8i/ch02_02.htm"]http://www.unix.org.ua/orelly/oracle/guide8i/ch02_02.htm[/URL]
[URL="http://www.unix.org.ua/orelly/oracle/guide8i/ch02_04.htm"]http://www.unix.org.ua/orelly/oracle/guide8i/ch02_04.htm[/URL]

Cheers!
Kalayama

execute the statment you are sure to commit from within the Autonomous block and commit. and all others normally. It does not matter what is the order of execution. If the transactions are rolledback it will not affect the transaction executed from within the Autonomous block. But for this you should know which transaction needs to be commited instantly,because that can't be rolled back.

just create savepoint there.

if u dont know How to using the PRAGMA AUTONOMOUS and PL/SQL programming ..
better to use save point to every SQL statment,then we can rollback to the particular area

[QUOTE=vijay.akki;19819]if u dont know How to using the PRAGMA AUTONOMOUS and PL/SQL programming ..
better to use save point to every SQL statment,then we can rollback to the particular area[/QUOTE]

What if i want to commit the 3rd transaction and rollback 1st and 2nd.
Can I use the savepoints for the purpose ?

:confused:

i have used
commit transaction;
in sql server 2005 but i am getting a error like
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
can some one solve this problem

Hi Satheesh,

In SQL server 2005, By default auto commit is on. So if you want to use the commit transaction, then you must have to use the begin and end transactions even to use the roll back.