Re: Calling user defined function in SQL where clause

> I require to call a function (for encryption..etc) inside a where
> clause of my sql.
>
> Example:
> select * from table
> Where col = my_func();
>
> The col is defined as a Varchar and this works fine as long as
> my_func() returns a string value.
>
> However, i need to change the sql to handle multiple values in the
> where clause, therefore i changed the sql to
> select * from table
> Where col in (my_func());
>
> now my_func() returns a string which has multiple strings separated by
> commas.
>
> This also works, as long as myfunc() returns 1 value. The moment i
> modify myfunc() to return multiple strings separated by commas, the
> sql fails to return any results. I tweaked myfunc() to return multiple
> values, with quotes, without quotes, embedded in double quotes..etc
> but to no affect.

that won't work because oracle will compare the value of col literally with the value the function f returns. so, that will be something like

  where col in ('value_1,value_2,value_3')

and not, as you expected

  where col in (value_1,value_2,value_3)    

> Is there any way, i can get this to work, or find a solution to this
> problem in another way?

However, if you're using 9i, try to do something like this:

create type vc_t is table of varchar2(100); /

create table t_ (
  a number,
  col varchar2(100)
);

insert into t_ values (1, 'one');
insert into t_ values (2, 'two');
insert into t_ values (3, 'three');
insert into t_ values (4, 'four');
insert into t_ values (5, 'five');
insert into t_ values (6, 'six');

create or replace function f return vc_t as   r vc_t := vc_t();
begin
  r.extend;
  r(1) := 'two';

  r.extend;
  r(2) := 'five';

  r.extend;
  r(3) := 'six';

  return r;
end;
/

select a,col from t_ , table(f) where col = column_value;

hth

Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html