CODEWITHSUNDEEP
oracle-database
P Sharma
P Sharma

Reputation: 2728

Commit after opening cursor in oracle

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

Upvotes: 1

Views: 10273

Answers (4)

yanan Ramirez
yanan Ramirez

Reputation: 1

Depends on your approach, for example in Pragma autonomous transaction could be desirable at the end of "entire" transaction to avoid partial information recorded , but when you need read another transaction in the same process and "release" info as fast as possible, you could make it with COMMIT. regards.

Upvotes: 0

APC
APC

Reputation: 146219

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.

Upvotes: 10

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 3

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

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?

Upvotes: 1

Related Questions