Re: PL/SQL: return lov with stored function?

In article <7rq4bg$pnp$1_at_nnrp1.deja.com>, <bdijkstr_at_my-deja.com> wrote:
>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:

You need to keep the sub-select construct, but you could have it call a stored function to return the values you want. If there are some values you need to reject but cannot do that in its where clause, then the function can return null or some other value you are sure will not match e.depid in the primary where clause.

Steve Cosner

>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;
>
>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.
Received on Thu Sep 16 1999 - 18:32:05 CEST