Parameter concatenation for IN condition

[sirpelon]

Hi, i'd like to know if it is possible to concatenate a parameter to a IN condition, something like this

SELECT *
FROM table
WHERE column1 IN parameter);

*column1 is of type NUMBER
*the value of parameter VARCHAR2 could be 4,5,6,8

once interpreted should be like :

SELECT *
FROM table
WHERE column1 IN (4,5,6,8);


When i send just a number (4), it's ok, but when i send two or more number (4,5,6) the error "NUMBER INVALID" is present

If anyone has a tip on this i'd be thankful

 

[sem]

Not explicitly, you need DYNAMIC SQL for that. Another option is to create some function that parses string parameter and returns some collection then replace your list by subquery from that procedure. But this approach needs some server pl/sql programming.

Regards, Dima

http://seminihin.narod.ru

 

[nagornyi]

Instead of

WHERE column1 IN (:parameter)

use

WHERE instr(:parameter, column1)>0