Commit after opening cursor in oracle.

Question

Can anybody suggest, whether we should use commit after opening and before closing cursor?

Answer 1

Well, it depends on what you are trying to accomplish. Sometimes you want to do that, sometimes you don't. Can you specify what are you trying to accomplish?

Answer 2

If the cursor locks records using FOR UPDATE, then all locks will be released by the commit. (In fact, any locks held are released by the commit.)

Also, you are more likely to get an "ORA-01555 Snapshot too old" error due to the "fetch across commit" - see this AskTom thread.

Answer 3

It is irrelevant in most cases.

A cursor is a construct for retrieving data. Once the records have been read it doesn't matter when in the process the cursor is closed, providing it is closed.

The COMMIT should be issued when the transaction is complete and not before.

The one case when the order of these two actions matters is when we are executing a CURSOR FOR loop. It is very important that any COMMIT should occur outside of this loop, that is before we open the cursor or after we close it. Otherwise we can have problems with read consistency. There are people who will argue that they have to commit inside the loop for some complicated reasons, but they are almost always mistaken.

The importance of this last case should not be overestimated. Most transactions should use SQL rather than DML inside a PL/SQL cursor, so it rarely applies.