ora-00604 --pls help

From: grace lim <mglim_at_softhome.net>
Date: Fri, 24 Nov 2000 16:02:12 +0800
Message-Id: <10690.122834@fatcity.com>

gurus,

i got a stored proc that consist of 30 for loops to open &update different 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 = 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 = 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 = bs.description
        where a.promo_code = 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  := 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 different tables and columns
for bs in (select ...)
loop
end loop; Received on Fri Nov 24 2000 - 02:02:12 CST