Re: :ORA-01000

From: Ken Johnson <kjohnson_at_ryback.com>
Date: 1997/03/27
Message-ID: <333A9619.298F@ryback.com>#1/1

Discart Miguel wrote:
>
> This message is crossposted to :
> comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
>
> Hi to all of you,
>
> I need your help, so here is my problem. On a form application
> (v4.5), I have a lot of fonction which call cursor to populate
> fields.
>
> I use these cursors always in a :
> FOR c1 IN CURSOR_NAME LOOP
> my_job
> END LOOP;
>
> The problem is sometimes I got an error ora-01000 (which mean to many
> cursor open at one time) but that never happen twice the same way. Is
> the meaning of this error that I don't understand or the use of the
> DO..LOOP.
>
> Anyone got a clue?
>
> TIA
>
> --
>
> ----------------------------------------------------------------
> Regards,
> Miguel Discart

I had this problem I while back.
I ran the following SQL from sql*plus to see what open cursors a session had open:

	select sql_text from v$sqlarea
	where address in
	 (select s.address from v$open_cursor s, v$session v
	  where s.saddr = v.saddr and v.sid = &1);

and there were a lot of things in there besides just the cursors I explicitly defined. (There were cursors that Forms was using to query records for various blocks, non-cursor pl/sql select statements - like "SELECT sysdate FROM dual", etc.)

What may be causing the sporatic nature of your problem is where the user has been before the error occurs. If they went straight to the screen that sometimes causes the error, then their would still be enough cursors left. But if they went to several other screens first, then their might not be enough left.

There is an initialization parameter that controls how many cursors each session can have open it is called OPEN_CURSORS. Currently our database is set at: OPEN_CURSORS = 200, and we haven't had any ORA-01000's in quite a while. I think originally it was at 50 (the default).

There is also an option: CLOSE_CACHED_OPEN_CURSORS=TRUE that will close any cursors that were opened, whenever a transansaction is committed (or rolled back). (There would be a slight performance hit when it had to recreate it for the next time it needed it.)

So to fix your problem, either change/add the OPEN_CURSORS= parameter or add the CLOSE_CACHED_OPEN_CURSORS=TRUE line in your init.ora file. I know the OPEN_CURSORS method works fine, I haven't tried the CLOSE_CACHED... method.

Ken Johnson Received on Thu Mar 27 1997 - 00:00:00 CST