PL/SQL: return lov with stored function?

From: <bdijkstr_at_my-deja.com>
Date: Thu, 16 Sep 1999 06:56:51 GMT
Message-ID: <7rq4bg$pnp$1_at_nnrp1.deja.com>

Hi all,
[Quoted] 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
[Quoted] 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; Unfortunately, this does not work. Tried several formats for the returning value. The where clause in the select just treats the returned value as one variable without expanding it.

Does anyone know if this is possible or have any hints?

Thanks very much,
Bart

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 16 1999 - 08:56:51 CEST