Re: Parameter type problem...

Harry,
I think the problem is that your input parameter is "site_id" but you also have a column named site_id in the amd_site_references table. Try changing your input parameter name and it should work. Liz

"Harry" <a_at_abc.com> wrote in message
news:ce3fatk1rn2st25m48cio69onbshtm02m2_at_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 - 19:09:43 CST