Re: Cursor limit reached

Sam Jordan wrote:

> Apparently a lot of people seem to have problems with cursors
> which aren't closed for some obscure reason. I'm joining the
> team now...
>
> I'm calling a stored procedure from a Java application several
> times, and after the n-th time (n always the same and matches the
> open_cursors value) I get the error saying that the maximal amount
> of cursors have been exceeded.
>
> I don't commit the transaction after every procedure call, but want
> to commit resp. rollback at last.

Meanwhile I have found out, that the explicit cursor isn't the problem. I have printed out V$OPEN_CURSOR during the operations. Two things were remarkable:

All implicit cursors used in the stored procedure were listed, so I was wrong to assume that the same cursor would be reused for all select statements. But these cursors seem to be released after each procedure call, at least, so that this can't lead to my problem, because the total amount of statements isn't that large for one call.

Calling the function itself also causes a cursor to be opened. V$OPEN_CURSOR
prints out the first 60 chars of the statement involved in the cursor, and it prints out something like

BEGIN :1 := MYPACKAGE.MYFUNCTION(:2,:3,:4,... And this cursor is the one which isn't freed. Maybe this problem has something to do with JDBC and the way it calls stored procedures. Currently I use the method execute() in class java.sql.CallableStatement.

I would still be happy if someone could explain what is the reason behind all this and how the problems can be solved.

bye
--
Sam Jordan Received on Tue May 18 1999 - 03:39:48 CDT