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?
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.
-> The Need
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)
-> Simple Solution
Put the SQL you need to commit inside a BLOCK (By using BEGIN and END) and commit only that block. As simple as that!
->PRAGMA AUTONOMOUS TRANSACTION
Well- this simply means the transaction block gets executed inependently - doesn't ensure commit unless specified within the BEGIN - END block.
debasisdas, 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.
http://www.unix.org.ua/orelly/oracle...8i/ch02_02.htm
http://www.unix.org.ua/orelly/oracle...8i/ch02_04.htm
Cheers!
Kalayama
Last edited by kalayama; 09-04-2007 at 06:13 PM. Reason: Typo
[COLOR="Blue"][SIZE="2"]"If you are not living on the edge of your life, you are wasting space"[/SIZE][/COLOR]
Someone says "Impossible is nothing". The man next him says "Let me see you licking your elbow tip!"
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
What if i want to commit the 3rd transaction and rollback 1st and 2nd.Originally Posted by vijay.akki
Can I use the savepoints for the purpose ?
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.
Posting Permissions
Reply With Quote