Re: explicitly closing cursors

Hello,

"Guillaume Mallet" <fritemayo_at_libertysurf.fr> wrote in message news:c5gvs1$fk9$2_at_news-reader5.wanadoo.fr...
> Hi,
>
> Version : Oracle 8.1.7.0.0
>
> I'm running a batch application that basically performs a potentially high
> number of SELECT queries (a minor proportion of UPDATEs as well), using
 the
> OCI.
> After some 300 statements get executed, I'm faced with the recurrent
> ORA-01000 error message that says "maximum open cursors exceeded". I
 checked
> my OCIHandleFree() calls, there's no mismatch, meaning they match the
 calls
> to OCIHandleAllocate().
>
> My understanding is that to close cursors, you have to
> - either let go of the connection, which i don't want to do because it'd
 be
> way too costly for me to open and close a connection on each request,
> - or as it says in the online documentation "explicitly close any open
> cursor during the execution of (my) program".
>
> After some amount of searching through documentation, my question is : how
> on earth do I explicitly close an open cursor ?

In OCI8, the cursor is closed by:

OCIHandleFree(stmthp, OCI_HTYPE_STMT);

However, the cursor will be not be closed immediately but on a subsequent round-trip to the server.
If the result set is exhausted by OCIStmtFetch, the cursor is closed automatically.
In some releases of 8i, there was a bug causing cursor leak but it should not exhibit itself under 8.1.7.4 and above.

> What leaves me somewhat perplexed is that i came across the following
> statement somewhere else in the online help : "Oracle 8i does not use
> cursors any more". Well my impression is that it jolly well does, if only
 to
> issue error messages related to their excessive number :-)

For some reason, the nice folks at Oracle decided to use the word "statement" ( in OCI8) instead of "cursor" which, uderstandably, causes a lot of confusion, but you are right, of course, it's still the same old cursor.

>
> Anyway, this is probably a quite simple resource-freeing problem, yet it
> gives me a lot of hassle. I'd be muchly grateful if anyone could help.

If you are quite quite sure about the handles being freed properly in your code, then it must be the bug in Oracle I mentioned above. Unfortunately, I do not remember its number. Tne number may not be important anyway because the only "solution" Oracle was able to come up with was upgrading to 8.1.7.4. and you might want to do it anyway to fix a host of other bugs.

VC Received on Tue Apr 13 2004 - 17:44:18 CEST