Results 1 to 10 of 10

Thread: Execute 3 Commit 1

  1. #1
    Junior Member
    Join Date
    Sep 2007
    Answers
    1

    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?


  2. #2
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Execute 3 Commit 1

    You need to user PRAGMA AUTONOMOUS TRANSACTION for the Purpose.


  3. #3
    Expert Member
    Join Date
    Sep 2006
    Answers
    477

    Re: Execute 3 Commit 1

    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!"

  4. #4
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Execute 3 Commit 1

    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.


  5. #5

    Re: Execute 3 Commit 1

    just create savepoint there.


  6. #6
    Junior Member
    Join Date
    Nov 2006
    Answers
    9

    Re: Execute 3 Commit 1

    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


  7. #7
    Moderator
    Join Date
    Jun 2007
    Answers
    2,074

    Re: Execute 3 Commit 1

    Quote Originally Posted by vijay.akki View Post
    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.
    Can I use the savepoints for the purpose ?


  8. #8
    Junior Member
    Join Date
    Oct 2007
    Answers
    1

    Re: Execute 3 Commit 1




  9. #9
    Junior Member
    Join Date
    Jun 2009
    Answers
    2

    Re: Execute 3 Commit 1

    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


  10. #10
    Contributing Member
    Join Date
    Apr 2008
    Answers
    31

    Re: Execute 3 Commit 1

    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

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact