Is this going to be a user who is going to pickup those 50000 values for use by the procedure via a reporting screen perhaps?. It seems very unlikely.

Another possibility is that a program which is going to feed these 50000 values? then it begs for a join with the source_table_with_50000 values /gtt lookup.

CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text);

INSERT INTO NAMES VALUES(1,'one,');
INSERT INTO NAMES VALUES(2,'two,');
INSERT INTO NAMES VALUES(3,'two,one,');
INSERT INTO NAMES VALUES(4,'three,');
INSERT INTO NAMES VALUES(5,'one,three');
COMMIT;

Now I fire below query.

SELECT * FROM NAMES where name like ('%one%','%two%');

then i get error so what's other way to search multiple value.

select *
from my_table t,
     gtt
where t.col like gtt.col||'%'

Hi, you can use:

SELECT
*
FROM
NAMES
WHERE
REGEXP_LIKE(NAME,'one|two')

ID NAME
-- -------------------------
1 one,
2 two,
3 two,one,
5 one,three

Thanks Connor,
I have tried second approach and it is really creating long SQL. Will try using GTT now.

I have a table where there are multiple values corresponding to one single value(1:n table)
I want to query and find out the parent value if all the supplied values exist for the parent.

for eg, for below set of data in a table:

name1 val1
name1 val2
name1 val3
name2 bval1
name2 bval2
name3 val98
name3 val99
name3 val100
name4 val100
name4 val10

i want to supply the below input, and expect the mentioned result:

abc, val2,val3      return name1
bval1               return nothing
val100,val10        return name4
val100,val10,val11  return nothing

I would like to know is this possible using an IN query or it'd require something else.

Thanks for your help.