Re: explicitly closing cursors

Guillaume Mallet wrote:
> 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().
>

Simply matching function calls in your source code isn't sufficient. Consider the following simple pseudo-code:

for ( 1 .. 5)

     handle_allocate;
end;
handle_free;

Not to insult your intelligence, but the point is, subtle problems of this nature involving subroutines and such can be hard to find. Another thing to check is errors... when an error happens, does the error handler close the 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 :-)
>

If you

ALTER SESSION SET SQL_TRACE TRUE; you should get a listing of the current open cursors dumped to a trace file when the error happens. This can help a lot with trouble-shooting.

--Mark Bole Received on Wed Apr 14 2004 - 02:21:14 CEST