RE: What's wrong with this cursor???

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Fri, 8 Sep 2000 14:06:52 -0700
Message-Id: <10613.116566@fatcity.com>

Never open a cursor in a loop and have the close outside the loop. Especially when you want to loop though the data in that table.

-----Original Message-----
From: Peter Hazelton [mailto:peterhazelton_at_hotmail.com] Sent: Friday, September 08, 2000 8:47 AM To: Multiple recipients of list ORACLE-L Subject: What's wrong with this cursor???

Hi all

I have created the following in Oracle Reports:

function CF_25_percentFormula return Number is total_rec number(9);
v_counter number(9) :=0;
v_icons inpatient.icons%type;
twenty_five_perc_rec number(9);

cursor this_cursor is
  select icons
  from inpatient
  where icons < 100;

begin
select count(icons)
into total_rec
from inpatient
where icons <=100;
twenty_five_perc_rec := total_rec*.25 ;
loop
open this_cursor;
fetch this_cursor into v_icons;
v_counter := v_counter +1;

if v_counter = twenty_five_perc_rec then return(v_icons); end if;
exit when this_cursor%NOTFOUND;
end loop;
close this_cursor;
END; The function compiles fine, but when I go to run it, I get the following error:
ORA_06511: PL/SQL: cursor already open.

Am I close with this function? I am looking to return the ICONS number for the 25th percentile record.

Thanks in advance for your help.

Peter

---

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at http://profiles.msn.com.

-- 
Author: Peter Hazelton
  INET: peterhazelton_at_hotmail.com

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

Received on Fri Sep 08 2000 - 16:06:52 CDT