Re: :ORA-01000 (too many open cursors)

Oracle forms does not close this type of cursor until the form itself is closed. One solution is to use a totally explicit cursor. Declare it up in the DECLARE section, but then use it like:

        OPEN c1;

	WHILE NOT c1%NOTFOUND LOP
		FETCH c1 INTO ...
		etc.
	END LOOP;
	
	CLOSE c1;

You can use c1%NOTFOUND to determine if you have tried to fetch the row after the very last row in the cursor. This will explicitly close the cursor for you, but it may then be a performance hit...

• kristian

Eric Hartzenberg <eric_at_erichome.demon.co.uk> wrote in article <S2mwsCAIEWQzEwUm_at_erichome.demon.co.uk>...
> In article <333A9619.298F_at_ryback.com>, Ken Johnson <kjohnson_at_ryback.com>
> writes
> >Discart Miguel wrote:
> >>
> >> This message is crossposted to :
> >>

comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.serve r,
> >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
> Single forms with lots of functionality (many blocks,lookups etc) can
> eat up your allocation of cursors pretty quickly. Particularly if the
> user keeps the form open all day (order-entry form , for example). Maybe
> you could put infrequently used features in subordinate forms. Closing
> these sub-forms releases the cursors associated with blocks.
> ________________________________________________________________
> | ~~~~~~~~~~~~~~~~~~~~ | ERIC HARTZENBERG | ~~~~~~~~~~~~~~~~~~~ |
> | Email : eric_at_erichome.demon.co.uk Compuserve : 100073,21 |
> | Homepage : www.erichome.demon.co.uk |
> |________________________________________________________________|
>
Received on Sat Apr 05 1997 - 00:00:00 CEST