Alternatives to using SQL IN in this query

Question

I have a string holding comma separated values = value1, value2, value3 .......

I want to perform following operation:

SELECT col FROM table WHERE col IN :values

This works fine if values have less than 1000 entries. It gives error when values have more than 1000 entries. There is a limit on usage of IN.

Is there any alternative way to perform this query?

EDIT: It is Oracle's Business Intelligence Publisher application. Customer/User can use any database underneath.

I do not have control over database. So I cannot create a temp table or stored procedure. All I can do is select multiple values from UI screen (it forms comma separated string) and use it in a SQL query. Depending on which reports are generated.

• Cannot use EXISTS with static string values.
• Cannot use stored procedure or temp table.

Answer 1

If you cannot create a global temporary table, then you could convert your delimited list to rows using sys.dbms_debug_vc2coll() and join to this collection.

SELECT t.col 
FROM table t
JOIN TABLE(SELECT column_value 
             FROM sys.dbms_debug_vc2coll(:values)) c on t.col = c.column_value;