Re: PL/SQL: Returning list of values with function

Just a thought...can't you use dynamic SQL to build up the select clause i.e. treat it as a cursor (dbms_sql package)?

Martyn

bdijkstr_at_my-deja.com wrote:

> Hi all,
>
> Hope anyone can help me with the following problem. I want a stored
> function to return a list of values, which I can use in a where clause.
> Without the function, it would look something like this:
>
> select e.name
> from employees e
> where e.depid IN (select d.id
> from departments d
> where d.location = 'AU');
>
> Now, I want to use the function instead of the subselect to build in
> some more logic. So, it would have to look something like this:
>
> select e.name
> from employees e
> where e.depid IN get_departments('AU');
>
> Function could look like this (simplified);
>
> CREATE OR REPLACE FUNCTION get_departments (loc in VARCHAR2(2))
> RETURN VARCHAR2
> IS
> v_id_list VARCHAR2(100);
> BEGIN
>
> some logic, which would result in:
>
> v_id_list := '1,2,3,4,5,6';
>
> return(v_id_list);
> END;
>
> Of course, this function would have to be put in a package, with the
> pragma restrict references WNPS and WNDS. Then, it does return the list
> as defined in the function, however, the select statement does not
> respond to it the way it should. It just treats the returned value as 1
> whole variable and does not expand to a list. Tried several things with
> the format of the returned value.
>
> Can anybody please advise if this is possible and give some hints.
>
> Would be very grateful for that,
>
> Thanks,
> Bart
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.

--
Martyn Reason
NZI9
B83 G36, The site formerly known as BT Labs. Received on Wed Sep 15 1999 - 03:08:21 CDT