Help with passing variables

[ddrozdov]

Hm,
in('JOHN', 'ANDY', 'KYM', 'PETER')
is not the same as
in('('JOHN', 'ANDY', 'KYM', 'PETER')')

'in' is a vararg operator each name is a different argument and you are only passing one.


Create this function

/* Checks whether a value is in the list of values */
 FUNCTION in_list
   (i_list VARCHAR2, i_string VARCHAR2,
    i_dlm VARCHAR2 := ',', i_repval VARCHAR2 := '@') RETURN BOOLEAN IS
  list VARCHAR2(999) := i_dlm||rtrim(ltrim(i_list,i_dlm),i_dlm)||i_dlm;
  string VARCHAR2(999) := i_dlm||i_string||i_dlm;
 BEGIN
   if instr(replace(list,string,i_repval),i_repval) <> 0
   then RETURN TRUE;
   else RETURN FALSE;
   end if;
 END in_list;

and call

in_list('JOHN,ANDY,KYM,PETER')

It will enable you to do what you want but I am not sure it'll speed things up for you because of all the context swithches between SQL and PL/SQL when calling this function.