COMMIT inside CURSOR-FOR-LOOP

hello,

Comitting inside a cursor-for-loop or fetch-loop results in oracle-error on subsequent fetches. This is true only if the CURSOR SELECT has FOR UPDATE clause. This is because COMMIT will release any locks held the session. However if the CURSOR SELECT does not have FOR UPDATE then this book: pl/sql guide by Scott Urman (oracle press) gives that there is no problem..

But i am told that COMMIT inside a fetch-loop for a CURSOR even if the SELECT does not have FOR UPDATE is not a good practise as there is problem with rollback segment if the data handled by the SELECT is huge..

I am not very clear about the above.. Any further clarifications, ideas welcome..

Rajesh Received on Thu Aug 14 1997 - 00:00:00 CDT