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

bsegonnes_at_free.fr (Bernard Segonnes) wrote in message news:<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

Bernard, are you getting errors while you execute? If so you need to do something like the following to close the cursor when an error occurred:

when others then

    if dbms_sql.is_open(src_cursor)

       then dbms_sql.close_cursor(src_cursor);     end if;

The second think I can think of is to verify that the code actually stored and running in the database has the close_cursor statement in it and not just the source you are looking at.

HTH -- Mark D Powell -- Received on Thu Mar 18 2004 - 15:51:44 CET