Parameter type problem...

From: Harry <a_at_abc.com>
Date: Thu, 08 Mar 2001 13:52:06 GMT
Message-ID: <ce3fatk1rn2st25m48cio69onbshtm02m2@4ax.com>

Using the following function (basically concatenates many fields together returning them in a single string - code very kindly given in this newsgroup!)

CREATE OR REPLACE function site_types ( site_id IN number ) return varchar2 as

   res varchar2(2000) := null;
   first boolean := true;
begin

   for curs in

       (SELECT p.DESCRIPTION FROM AMD_SITE_REFERENCES r, AMD_PICKLISTS p WHERE ((p.ID = r.SITE_TYPE_ID) AND (r.SITE_ID = site_id)))

   loop

      if not first then
         res := res || ',';
      else
         first := false;
      end if;
      res := res || curs.DESCRIPTION;

   end loop;
return res;
end site_types;
/

The problem is on the sample data I'm using the select statement should return 1 row & does if the parameter site_id is replaced with a hard coded value in the select statement - if I call the function like "select site_types(1) from dual" it returns more than one row - in fact rows that do not match the (r.SITE_ID = site_id) clause!

What the hell is going on? - this is really doing my head in! - is there a problem with the way I declared the site_id parameter or what?

many thanks

harry Received on Thu Mar 08 2001 - 07:52:06 CST