Re: Cursors consumed to fast :-(

In <1uuJzA9FBh108h_at_dream.hb.north.de> ms_at_dream.hb.north.de (Martin Schroeder) writes:
>Hi,
>I have constructed a PL/SQL program consisting of some PL/SQL packages that
>uses implicit cursors and explicit cursors from DBMS_SQL. Mostly the cursors
>are used implicitely via Loops; at the places where the cursors are used
>explicitely (via DBMS_SQL) they are closed after use.
>The program currently does no COMMIT; it ROLLBACKs in case of errors.
 

>I call the program/routines via execute statements from SQL*Plus.
 

>But I frequently get the ORA-01000 error -- maximum number of open cursors
>exceeded. OPEN_CURSOR is set to 50.
 

>When I examine the cursors via v$open_cursor I find indeed many cursors (e.g.
>from triggers); but these remain even after an explicit COMMIT or ROLLBACK. :-((
 

>I am quite sure that I close all cursors the program knows of...

Here's the update from today:
If I input a simple package that contains only one procedure which does only a FOR LOOP over a simple select cursor -- and the loop body is null -- and run the procedure via execute package.procedure from SQL*Plus, then after the run I see the cursor in v$open_cursor.

If I run the code in the procedure from an anonymous block I don't have an entry in v$open_cursor.

I think the cursor from the package is a problem -- but I have no idea how to get it closed properly.

Any clues?

Best regards

        Martin

-- 
               Martin Schr"oder, MS_at_Dream.HB.North.DE
- If I start up a C shell and put it up to my ear, what will I hear?
- You'd hear the sound of the C, of course, as it repeatedly crashes
       on the silicon beach.  (The Usenet Oracle, Jan 1993)