PL/SQL: Returning list of values with function

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. Received on Tue Sep 14 1999 - 19:45:36 CDT