oracle 10g IN clause query

Question

Please ignore obvious syntax flaws in the below:

I have an sql like this as a named query:

select saalry from emp where emp_id in (:id)

id is of type number I wanted to pass in a comma separated list like this:

String id = 121,123,456

But I am getting ORA-01722: invalid number How can I pass a comma separated list of ids to my IN clause?

Answer 1

Assuming :id is a string containing a relatively short comma-delimited list of numbers (e.g. '123,456,789'), this may be sufficient for you:

select saalry from emp
where INSTR( ',' || :id || ','
           , ',' || TRIM(TO_CHAR(emp_id)) || ','
           ) > 0;

It won't perform as well, however, since it is unlikely to use an index on emp_id.