Re: Cursor managment with dynamic sql in Pro*C

No being able to see your code this is hard.

My experience with Oracle has been to never even loop at the cursor allocation.

My experience with cursor problem like too many open cursors was just that. If you run a stored procedure and do not close the cursor, it is still considered open. I have accidentally run out of cursors when only one was opened per stored procedure. This does generate a specific Oracle error.

I would guess that you have a bug. Examine the code. Look for an easy problem -- not a hard one.

Mike Krolewski

Tony Okrutny wrote:

> 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 Sat Jan 23 1999 - 23:26:11 CST