What is meant by CURSOR WITH HOLD

lmadhavi · New User Joined: 01 Mar 2006 Posts: 10

can any one of you please explain what is meant by CURSOR WITH HOLD.
and is the purpose of it?

guptae · Posted: Fri Jun 09, 2006 2:02 pm

Hi,

If we declare the cursor with hold option will remain open even if ur program execution is complete.
All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors.
In order to close this cursor u need to explitly close it(Close cursor)

Hope it will helpful

twissi · Posted: Fri Jun 09, 2006 2:56 pm

HI lmadhavi,

What happens is your CURSOR will get closed when it executes a COMMIT statement and then your program tries to FETCH that CURSOR, you'll get an error "CURSOR in a FETCH or CLOSE statement is not OPEN", SQL = -501.

CURSOR WITH HOLD option is used to get rid of this difficulty.

Hope I'm clear.

Cheers, Twissi.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi twissi,

I completely agree with you.

Just to add to that, all cursors opened in the program gets closed and resources are released when program ends. In fact, commit is issued automatically when program ends.

Thanks,
Parikshit

prasadvrk · Posted: Fri Jun 09, 2006 6:24 pm

In other words you have to declare a CURSOR with WITHHOLD option if you want to process the rows fetched even after COMMIT(If you dont explicitly COMMIT , COMMIT will be issued after the completion of LUW, Logical Unit Of Work)

twissi · Posted: Fri Jun 09, 2006 7:44 pm