If I have commit command inside a cursor loop, does it close the cursor prematurely or not ?
Printable View
If I have commit command inside a cursor loop, does it close the cursor prematurely or not ?
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.
No it will not. And it is sometimes a good idea, to avoid too much rollback, depending on what you're doing . . .
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