Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: ora-00604 --pls help

Re: ora-00604 --pls help

From: <nlzanen1_at_ey.nl>
Date: Fri, 24 Nov 2000 12:32:17 +0100
Message-Id: <10690.122845@fatcity.com> 





Hi



Max_open_cursors could easily be set a lot higher than 50. (I have 1000=
)



Probably  every bit of recursive sql is considered a cursor as well



Jack






grace lim <mglim_at_softhome.net>@fatcity.com on 24-11-2000 09:05:27



Please respond to ORACLE-L_at_fatcity.com



Sent by:  root_at_fatcity.com




To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:


Subject:  ora-00604 --pls help



gurus,




i got a stored proc that consist of  30 for loops to open &update diffe=
rent


tables. when i run it the ff error occurs:
 ora-00604 error occured at recursive sql level.
 ora-0100-: maximun open cursors exceeded.



i'm the only one running the process...currently max open cursor =3D 50=



i've checked all the cursors i've openned and have closed it.... is the=



close cursor wrongly placed?


i know that for loop automatically close the cursor it opens.




here is my sample stored proc.




PROCEDURE wrk2 IS



cursor bs_cur (bs_promo_code varchar2) is


   select a.sid  from slots  a


    where a.promo_code =3D bs_promo_code a;



cursor j is


.....



id  number;


err_txt VARCHAR2(240) ;



BEGIN


for bs in    (select * from slots_work a)



  loop


    open bs_cur(bs.promo_code);


    fetch bs_cur into id;


    if bs_cur%found then

       update slots a
                  set a.description =3D bs.description
        where a.promo_code =3D bs.promo_code ;
    else
       begin
              select pro1_seq.nextval into id from sys.dual;
                   insert into bingo_slots(promo_code,description)
                   values(bs.promo_code,bs.description);

      exception
                  when others then
                  err_txt  :=3D DBMS_ERROR_TEXT;
                     insert into exceptions
                     values(exc_seq.nextval,err_txt);
                 end ;




    end if;  --- found


    close bs_cur;


end loop;



---code here is exactly the same as above except it select &opens diffe=
rent


tables and columns


for bs in (select ...)


loop


end loop;



end;


Grace Lim


Suy Sing Comm'l Corp.


247-41-34

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: grace lim
  INET: mglim_at_softhome.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).






=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie =
aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &=

Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, n=
och
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat e=
en
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden=
.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u=

vriendelijk doch dringend het e-mailbericht te retourneren aan de verze=
nder
en het origineel en eventuele kopie=EBn te verwijderen en te vernietige=
n.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algeme=
ne
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. D=
e
algemene voorwaarden worden u op verzoek kosteloos toegezonden.


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communicati=
on
without the authority of Ernst & Young. Ernst & Young is neither liable=
 for
the proper and complete transmission of the information contained in th=
is
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained =
nor
that the communication is free of viruses, interceptions or interferenc=
e.

If you are not the intended recipient of this communication please retu=
rn
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms an=
d
conditions, which contain a clause that limits its liability. A copy of=

these terms and conditions is available on request free of charge.


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D








=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

De informatie verzonden in dit e-mailbericht is vertrouwelijk, uitsluit=
end
bestemd voor de geadresseerde en mogelijk beschermd door een beroepsgeh=
eim.
Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van =
deze
informatie aan derden is, behoudens voorafgaande schriftelijke toestemm=
ing
van Holland Van Gijzen advocaten en notarissen, niet toegestaan. Hollan=
d
Van Gijzen advocaten en notarissen staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, n=
och
voor tijdige ontvangst daarvan. Holland Van Gijzen advocaten en notaris=
sen
kan niet garanderen dat een verzonden e-mailbericht vrij is van virusse=
n,
noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenko=
mst
van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u=

vriendelijk doch dringend het e-mailbericht te retourneren aan de verze=
nder
en het origineel en eventuele kopie=EBn te verwijderen en te vernietige=
n.

In de Nederlandse vestigingen wordt de praktijk uitgeoefend en worden
opdrachten slechts aanvaard en uitgevoerd door Holland Van Gijzen advoc=
aten
en notarissen, een maatschap van besloten vennootschappen. Een lijst va=
n
partners wordt op verzoek beschikbaar gesteld. Holland Van Gijzen advoc=
aten
en notarissen hanteert bij de uitoefening van haar werkzaamheden algeme=
ne
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. D=
eze
voorwaarden zijn op 19 januari 2000 gedeponeerd ter griffie van de
arrondissementsrechtbank te Rotterdam onder nummer 18/00 en te vinden o=
p
http://www.hollandlaw.nl/


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

The information contained in this communication is confidential, intend=
ed
solely for the use of the individual or entity to whom it is addressed =
and
may be protected by professional secrecy. You should not copy, disclose=
 or
distribute this communication without the authority of Holland Van Gijz=
en
advocaten en notarissen. Holland Van Gijzen advocaten en notarissen is
neither liable for the proper and complete transmission of the informat=
ion
contained in this communication nor for any delay in its receipt. Holla=
nd
Van Gijzen advocaten en notarissen does not guarantee that the integrit=
y of
this communication has been maintained nor that the communication is fr=
ee
of viruses, interceptions or interference.

If you are not the intended recipient of this communication please retu=
rn
the communication to the sender and delete and destroy all copies.

In the Netherlands offices the practice is conducted and instructions a=
re
solely accepted and carried out by Holland Van Gijzen advocaten en
notarissen, a professional partnership consisting of private limited
liability companies. A list of partners is available on request. In
carrying out its engagements, Holland Van Gijzen advocaten en notarisse=
n
applies general terms and conditions, which contain a clause that limit=


Received on Fri Nov 24 2000 - 05:32:17 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US