Cursor managment with dynamic sql in Pro*C

From: Tony Okrutny <tokrutny_at_tenebral.com>
Date: Wed, 20 Jan 1999 14:04:00 -0500
Message-ID: <7858g4$429$1@camel21.mindspring.com>

I have a program that uses about 25 cursors. 1 of the cursors is a dynamic sql select. The problem I am having is that Oracle decides to close my cursor and re-use its cursor cache entry for another cursor( this other cursor is different depending on what operations I perform). This would be expected had I ever closed the dynamic sql cursor. However, I have only opened and performed 1 or more fetches on that cursor.

I have been able to keep Oracle from closing and re-using the cursor by setting HOLD_CURSOR to yes, but I would like to know why this was happening in the first place. My MAXOPENCURSORS is set to 40 and my OPENCURSORS is at 150 so with 25 cursors I wouldn't expect cursor re-use to be an issue.

If anyone knows any reason why this may be happenning, please let me know.

Tony Okrutny Received on Wed Jan 20 1999 - 13:04:00 CST