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 */
(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;
if instr(replace(list,string,i_repval),i_repval) <> 0
then RETURN TRUE;
else RETURN FALSE;
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.
Last edited by ddrozdov; 05-05-2004 at 12:04 AM.
Your private mailbox is full.
I can't reply to your message.
Can you do me a fovo r/ur that I asked you about?
Now that I think about it, you might want to switch the return datatype to number 0,1 instead of boolean. The SQL might complain otherwise.
Click Here to Expand Forum to Full Width