SUMMARY: Debugging Cursors (context areas)

I received several helpful replies to my message regarding persistent "maximum open cursors exceeded" errors within SQL*Forms despite taking care to close any explicitly opened cursors. Thank you all for your time and help. The messages that I received are summarized below.

The bottom line is that the problem is most likely due to bugs in the current release of SQL*Forms v3.0. Increasing the INIT.ORA parameter max_cursors to 255 seems to have eliminated the problem. (Phew!)

We also resolved the "error occurred at recursive SQL level 1" problem that I mentioned in my original message. This turned out to be caused by using a DML statement (in our case, an INSERT) in a (non-commit-time) trigger that should not be using DML statements (the restrictions are covered in the Forms documentation, but are not enforced by the PL/SQL compiler or Forms runtime).

                Thanks again,

                Carl

SUMMARY OF MESSAGES FOLLOWS:

(1) From dlm_at_hermes.dlogics.com (Dave Mausner, Sr Consultant / Datalogics Inc):

>This is a persistent problem in all versions of SQL*Forms. Many cursors are
>opened for data dictionary access "behind your back", so you cannot blame
>yourself for all of the open cursors.

     >


	> My solution was to set max_cursors = 255 in the INIT.ORA.  This creates some


	> additional memory usage within each user process, but not enough to cause

	> concern.

(2) From jht_at_cs.tut.fi (Harkki Jukka, Tampere University of Technology):

>You are running out of runtime resources. There is three approaches to
>solve this problem:
>
>1) Change your application to better utilize resources.
>
>2) Run your forms in optimized mode 'runform30 -o ...'. This affectes
> to cursor allocation.
>
>3) Increase available resources. For instance use maximum max_open_cursors=
> 255. According to Oracle Finland these resources do not cost in terms
> of memory.
>
>I had an similar problem with forms 2.3 and I solved it by running forms
>in optimized mode and by increasing available resources. After migrating
>to forms 3.0 i had an extra problem while I was trying to commit things:
>I ran out of savepoints. I use several forms layered by CALL_FORM command
>an every form makes a savepoint before calling a new form.

(3) From Andrew Babb <Andrew.Babb_at_vuw.ac.nz> (Victoria University of Wellington):

>It is very common for open_cursors ( not max_cursors ) to be set to 255, and
>not the default value of 50. This will certainly get rid of your second error.

(4) From pytlik_at_cs.umb.edu (Marek Pytlik, University of Massachusetts at Boston):

>I had similar problems and found out that they were related to not closed
>cursors in while loop.

(5) From Oracle Support:

    When I was finally able to get ahold of a knowledgeable analyst at Oracle     Customer Support, I found out that there are several bugs in the current     version of SQL*Forms v3.0 that are most likely causing this problem.     These bugs result in the failure to properly release implicit cursors used     by select (and other) PL/SQL statements. Their recommended solution was to     increase max_cursors to 255, as several of you suggested. Received on Tue Mar 03 1992 - 00:22:39 CET