explicitly closing cursors

[Guillaume Mallet]

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 OCIHandleAlloca te().

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 ?
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 :-)

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.

G -

[VC]

Hello,

"Guillaume Mallet" <fr*******@libe rtysurf.fr> wrote in message
news:c5******** **@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 OCIHandleAlloca te().

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(s tmthp, 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