CURSOR & COMMIT ( Urgent )
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: CURSOR & COMMIT ( Urgent )

  1. #1
    Join Date
    Nov 2000
    Posts
    416
    If I have commit command inside a cursor loop, does it close the cursor prematurely or not ?
    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Nov 2000
    Posts
    344
    I don't think it will close your cursor, but you will probably find that it performs better if you save your COMMIT until after the loop... I think if your cursor is through a dblink it will close it, but otherwise, it will not.

  3. #3
    Join Date
    Aug 2000
    Posts
    462
    No it will not. And it is sometimes a good idea, to avoid too much rollback, depending on what you're doing . . .

  4. #4
    Join Date
    Nov 2000
    Posts
    416
    Hey guys, I found the answer to my question by myself !!! from Oracle metalink, looks like you are wrong they said it's blow out look ::::

    FROM Oracle metalink search result :

    COMMIT inside Cursor FOR Loops

    A commit must be issued after the loop otherwise ORA-1002(fetch out of sequence) will be received. A commit invalidates a cursor and futher fetches will not be performed.


    I'm a relative newbie to PL/SQL. I saw the thread about "Fetch Across Commit" but that concerned a cursor defined with a SELECT..FOR UPDATE clause. My question is, For
    vanilla-style cursors, is it bad practice to COMMIT inside a cursor FOR loop? Seems like it would be just from a logical point of view, since you would probably want to either
    COMMIT or ROLLBACK a logical unit of work. Also, would it tend to cause ORA-1555 errors if the transaction was large? For example, a large cursor is opened, each record is
    processed, and the table being updated has a couple of triggers; so does the rollback segment start marking as inactive extents which will be needed by the cursor query because of
    the COMMIT?

    Feurstein's book describes the implicit OPEN/FETCH/CLOSE in cursor FOR loops. So how does the use of COMMIT inside the FOR loop affect processing? Does it close the
    cursor only to have the FOR open it again, or what? Here's a sample of what we're using:

    CURSOR c_il_indvdl_lic IS
    SELECT key_indvdl_lic
    FROM il_indvdl_lic, il_indvdl_ce, sy_busn_drvn_cd
    WHERE txt_busn_drvn_cd_ctgy = app_glbl_parm.s_status_ctgy_il
    AND ;

    FOR lc_il_indvdl_lic IN c_il_indvdl_lic
    LOOP
    BEGIN
    UPDATE il_indvdl_lic
    SET txt_status = app_glbl_parm.s_status_inactv,
    dte_status_usr_dt = SYSDATE
    WHERE key_indvdl_lic = lc_il_indvdl_lic.key_indvdl_lic;
    COMMIT;
    END;
    END LOOP;

    Any advice would be greatly appreciated.

    Thanks.



    From: Oracle, krishna kumar sivasubramanian 10-Aug-00 14:41
    Subject: Re : COMMIT inside Cursor FOR Loops

    Hello

    A commit must be issued after the loop otherwise ORA-1002(fetch out of sequence) will be received. A commit invalidates a cursor and futher fetches will not be performed.

    Regards
    S.Krishna Kumar
    An ounce of prevention is worth a pound of cure

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width