Re: MAX_OPEN_CURSORS - How to set on W95/NT? Followup

In article <344E77A8.1BCA_at_bose.com>,
  "Joseph D. Sumalbag" <joseph_sumalbag_at_bose.com> wrote: <oldest text moved below>
> Not so fast Steve ....
>
> First try to check if your application is opening
> cursors without closing them .... It could be a loop
> with an open cursor procedure on it and the one who coded
> it might have fogotten to close it .
>
> By this time that procedure may have not have reached
> the the open_cursor parameter value but if it is bouncing over
> and over again then you'll have the same problem over and over
> again.
>
> After all 200 is a large value for an open cursor for each
> user per session , specially if you have less than 200 tables
> and you don't have all of them open . But then again it depends
> upon your application .
>
> This is just one aspect to look at ....

My original setting for the open_cursors was the as-released default of 50. That was the limit where the problem showed up. I usually set it to 100 and everything works fine, but I chose 200, just in case...

Our forms are pretty big. One I just completed has 10 base-table blocks and 35 LOV's. And on these, the developer has no control over opening and closing cursors. I don't know why Oracle doesn't close a cursor immediately if it has fetched all rows. Everywhere else we use selects in the pl/sql code, we ALWAYS open, fetch, then close the cursors. We can control the pl/sql cursors and believe me, they are closed religiously--I wrote the code.

So I was bumping up against the limit of 50. I will probably never have a problem with 200 limit.

Thank you for writing.

Steve Cosner
>
> Steve Cosner wrote:
> >
> > I want to thank all who responded to my question. The correct answer for
> > my Windows95 PC is to set OPEN_CURSORS to 200 (not Max_open_cursors) in
> > file C:\ORAWIN95\DATABASE\INITORCL.ORA The "ORA-01000 Maximum open
> > cursors exceeded" and ORA-00604 messages are gone.
> >
> > Thanks,
> > Steve Cosner
> >
> > In article <877194349.8460_at_dejanews.com> stevec_at_zimmer.csufresno.edu
> > wrote:
> > > I am trying to set up my home pc running Personal Oracle 7 for
> > > Windows95 so I can do a demo of some heavy duty forms. These require
> > > boosting the max_open_cursors setting since I keep getting the
> > > ORA-00604 too many open cursors message.
> > >
> > > I have gone into the Registry and added a string: MAX_OPEN_CURSORS,
> > > and set the value to 200. I tried this at the
> > > HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE level, and below this at the
> > > ORACLE_HOMES level and the ORACLE1 level below that. (Actually, I
> > > have an entry at each level) But after restarting Windows, PO7, and
> > > the Form with the problem, I still hit the limit at the same place.
> > >
> > > Where is the right place to put this entry?

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet