Limited number of cursors in Oracle 9.2.0 on Linux ?

From: Bernard Segonnes <bsegonnes_at_free.fr>
Date: 18 Mar 2004 01:48:23 -0800
Message-ID: <cc913d8.0403180148.793d74be@posting.google.com>

Hi,

In my '.pc' I call several undred of times stored procedures which then call 2 other stored procedures. I only use a cursor in the last called procedure :

CREATE OR REPLACE PROCEDURE EXECUTE_SQL (commandeSQL IN VARCHAR2 ) AS  c number;
 n number;
 BEGIN
 c := dbms_sql.open_cursor;
 dbms_output.put_line('run= ' ||commandeSQL );  dbms_sql.parse(c, commandeSQL, dbms_sql.native);  n := dbms_sql.execute(c);
 dbms_sql.close_cursor (c);
 commit;
END;
/

As you can see I close my cursor.

If I run a small loop : every thing is fine. The problem occurs if my loop is too big : I got the ORA-01000 error, Maximun number of cursors reached.

I have increased the OPEN_CURSORS in ini.ora from 100 to 200, but why Oracle doesn't reuse my previous freed cursors ? Moving the OPEN_CURSORS parameter from 200 to 1000 will move the problem, but not resolve it.
Is there a CLEAN way for Oracle to manage cursors ?

Thanks for your answers.

Bernard Segonnes

---

bsegonnes_at_free.fr
http://bsegonnes.free.fr Received on Thu Mar 18 2004 - 03:48:23 CST