Re: ORA-01000 max cursors exceeded

azp74_at_hotmail.com wrote:
> Hi
>
> My app is written using the OCI running against a 9i 9.2.0.1.0 server
> running on Windows XP SP2.
>
> I (& my users) keep on encountering the hoary old ORA-01000 problem.
> The stored procedures in the database use a lot of ref cursors to
> return result sets. Where possible, in the stored procedures, I
close
> the cursors and where they are returned to the application I have
tried
> a couple of things:
>
> 1. I have made my cursors scrollable and then cancelled them by
> calling OCIStmtFetch2 with nrows set to 0.
>
> 2. I have made my cursors non scrollable and always loop through
> fetching til I hit 1403 (NO_DATA), which, according to the
> documentation (OCI), implicitly cancels the statement handle.
>
> Either way I still have the cursors I returned from PL/SQL stored
> procedures hanging around AND I have the implicit cursors PL/SQL
opens
> from standard SQL queries.
>
> If I query v$mystat for the number of open cursors it always seems
> quite low. I understand that PL/SQL caches its implicit cursors in
> such a way that they shouldn't be affecting my overall cursor count
so
> I just don't understand why I keep running out!
>
> The application communicates with the database almost exclusively by
> using stored procedures, so I feel it must be something I am not
doing
> in the OCI code which isn't freeing up the server side cursor
resource.
>
> It seems as though the web is full of people experiencing this
problem
> but there are very few useful OCI based responses so I hope someone
can
> help!
>
> Thanks
> Alex

What's the value of open_cursors init parameter in your database?

Regards
/Rauf Received on Wed Feb 09 2005 - 09:39:05 CST