oracle 10g IN clause query

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:

Strin开发者_如何学Gog 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?

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.

There is another way that is from http://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement

Their example is

select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

Which can be put into an in clause

 select * from emp where ename in (
   select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
   connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null );