0

I am getting passed comma separated 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 inefficient.

Any pointers?

BenMorel
  • 34,448
  • 50
  • 182
  • 322
dmitry
  • 3
  • 2

3 Answers3

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>)))
/
Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
  • but in that solution you never show what `str_to_number_tokens` looks like? – dmitry Jun 15 '10 at 22:00
  • @dmitry - Perhaps I was too subtle but there is a link embedded in the sentence "I am using a variant of Anup Pani's implementation," i.e. http://anuppani.blogspot.com/2007/07/tokenizer-in-oracle-plsql.html – APC Jun 16 '10 at 01:17
1

This solves exactly same problem

Ask Tom

josephj1989
  • 9,509
  • 9
  • 48
  • 70
0

In 11g you can use the "occurrence" parameter of REGEXP_SUBSTR to select the values directly in SQL:

select regexp_substr(<csv values passed in>,'[^,]+',1,level) val
from   dual
connect by level < regexp_count(<csv values passed in>,',')+2;

But since regexp_substr is somewhat expensive I am not sure if it is the most effective in terms of being the fastest.

Jens Krogsboell
  • 1,093
  • 11
  • 18