Help Debugging CURSORS (context areas)

Hi. I'm helping a friend debug an Oracle application coded in SQL*FORMS v3, and we've been repeatedly encountering a nasty problem involving cursors (i.e. context areas). The application seems to work properly, except that after dozens of high-level operations (such as adding a customer or an order) are performed, we inevitably get one of the following errors:

	(most common)  ORA-00604: error occurred at recursive SQL level 1
                       (this often occurs during an INSERT that is performed by forms)

        (also common)  ORA-01000: maximum open cursors exceeded
                       FRM-40735: trigger raised unhandled exception INVALID_CURSOR

Since max_cursors is set to 50 in init.ora, it appears that somewhere cursors are being opened and are not being properly closed. However, we are only using explicit cursors in a small number of places. One involves a FOR loop to fetch records (and thus the open and close are implicit), and the others are simply to test if there are any records that meet a certain criteria, using cursor%FOUND. In these cases, we open the cursor, save cursor%FOUND in a boolean variable, and then immediately close the cursor.

So, the bottom line is that we're puzzled. We have a call in to Oracle Tech Support, but so far they haven't been very helpful. So I suppose my questions are:

• Has anyone experienced (and hopefully resolved) a similar problem?
• Does anyone have any debugging tips or techniques that may be useful?
• Any ideas about where to look for problems involving implicit (SQL) cursors?

We are using Oracle on a Sun4 under SunOS 4.1.1, running RDBMS v6.0.33, PL/SQL v1.0.32, and SQL*FORMS v3.0.16.

Any information or pointers would be greatly appreciated. Please send messages as e-mail to carl_at_meson.lcs.mit.edu, or post to this newsgroup. I will summarize and post any messages received via e-mail. Thanks for your time and help.

• Carl Waldspurger