Good way to deal with comma seperated values in oracle

Question

I am getting passed comma seperated values to a stored procedure in oracle. I want to treat these values as a table so that I can use them in a query like:

select * from tabl_a where column_b in (<csv values passed in>)

What is the best way to do this in 11g?

Right now we are looping through these one by one and inserting them into a gtt which I think is ineffecient.

Any pointers?

Answer 1

Oracle does not come with a built-in tokenizer. But it is possible to roll our own, using SQL Types and PL/SQL. I have posted a sample solution in this other SO thread.

That would enable a solution like this:

select * from tabl_a 
where column_b in ( select * 
                    from table (str_to_number_tokens (<csv values passed in>)))
/

Answer 2

Hi This solves exactly same problem

Ask Tom