Re: Limited number of cursors in Oracle 9.2.0 on Linux ?

On Thu, 18 Mar 2004 17:48:23 +0800, Bernard Segonnes wrote (in article <cc913d8.0403180148.793d74be_at_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

try moving your commit

commit;
 dbms_sql.close_cursor (c); Received on Wed Mar 24 2004 - 00:34:45 CET