Opening multiple cursors in a loop in Oracle

From: Mr Not So Know It All <djscratchnsniffing_at_yahoo.com>
Date: 18 Apr 2005 10:29:26 -0700
Message-ID: <1113845366.846062.146660@o13g2000cwo.googlegroups.com>

Im a newbie. Please help if you can.

basically, im getting this error from SQL Plus

ERROR at line 1:

ORA-06511: PL/SQL: cursor already open
ORA-06512: at "SAFETYUSER.SELECT_RS", line 62
ORA-06512: at "SAFETYUSER.SELECT_RS", line 77
ORA-06512: at line 9

here is my code

procedure sel_Obs
  (
    cuid in varchar2,
    wkPerf out t_wkPerf,

    obsID out t_obsID,
    stamp out t_stamp,
    itemV out t_itemV,

    itemID out t_itemID
   )
IS

• row count for number of itemV in observations rCount number default 0;
• loop counter pcount NUMBER DEFAULT 1;
• work performed in 0022 into select into wPerf varchar(50);
• cursor to get obsID for entered cuid CURSOR cur_obsID IS select "0022".obsID from "0022" where mgrcuid = cuid;

  cursor cur_ID_Obs(par_ObsID varchar2) is -- get id number from observation per 0022 obsID
  select id, stamp,itemID
  from observations
  where obsid = par_obsID; -- par_obsID gets obsID

• get itemId from observation per 0022 obsID and Deviated cursor curDev(parObsID varchar2) is select itemID from observations where obsid = parObsID ; -- par_obsID gets obsID

  cursor cur0022 is

  select ecuid <-------------------------------------line 62

  from "0022"
  where obsid = 'txcqkzg3698';

  obsRow cur_ID_Obs%rowtype;
  obsDevi observations.itemID%type;
  obsECUID "0022".ecuid%type;

BEGIN   FOR sel_Dev_lp IN cur_obsID
  LOOP
    open cur_ID_Obs(sel_Dev_lp.obsID); -- pass obsID to cur_ID_Obs     fetch cur_ID_Obs into obsRow;
    close cur_ID_Obs;

    open cur0022; <---------------------------------------------line 77

    --open curDev('txcqkzg3698'); -- pass obsID to cur_ID_Obs     --fetch curDev into obsDevi;

    select count(*) into rCount
    FROM Observations
    WHERE ObsID=sel_Dev_lp.obsID and itemV=2;

    select workperf into wPerf
    FROM "0022"
    WHERE ObsID=sel_Dev_lp.obsID;

    wkPerf(pcount) := wPerf;
    obsID(pcount) := sel_Dev_lp.obsID;
    stamp(pcount) := obsRow.stamp;
    itemID(pcount) := 1;
    if rCount = 1 then
      itemV(pcount) := 0;
    else

        itemV(pcount) := 1;
    end if;
    pcount := pcount + 1;
    --close curDeviation;
  END LOOP; END; if i remove line 62 (open cur0022) and put it outside of the loop, it works. can you run two cursors in a loop? or am i missing something else? Received on Mon Apr 18 2005 - 12:29:26 CDT