Re: explicitly closing cursors

From: Joe <joe_at_billydog.com>
Date: Tue, 20 Apr 2004 14:07:32 -0500
Message-ID: <108atali5ab17eb@corp.supernews.com>

This drove me nuts way back when.

I took "excplicitly closing" a cursor to mean something like:

    Open Whatever_Cursor;
    Loop

        Fetch ....
        Exit when Whatever_Cursor%NotFound;
        blah.....

    End Loop;
    Close Whatever_Cursor; <==== Explicitly closing cursor

But from what I remember, I checked every explicit cursor and made sure I was closing them, to no avail.

Upping max_cursors (not sure the parameter) to like 750 or so seemed to fix it.

Doesn't seem to be a problem after 8i.

Hope this helps.

Joe

"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 ?
> 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 -
>
>
>
>
>
>
Received on Tue Apr 20 2004 - 14:07:32 CDT