0

I want to pass multiple values via a single parameter.

select 
id,
item_number 
from AGILE.item_p2p3_query where subclass='2477110' and date32 is not null 
and item_number in : p **// passing one parameter would work. But when I pass two parameters like EN 60439-1:1999,EN 60439-3:1991 doesn't seem to work**
--in ('EN 60439-3:1991','EN 60439-1:1999') // this will work

Kindly suggest only SQL suggestion and not PL/SQL as I would be using this in a report.

Niranjan
  • 172
  • 1
  • 5
  • 19
  • 1
    Maybe this post may help http://stackoverflow.com/questions/13580245/sending-an-array-of-values-to-oracle-procedure-to-use-in-where-in-clause – A.B.Cade Dec 20 '12 at 11:36

2 Answers2

2

I assume you are binding the values to the query, as it works when there is one value, but fails when there are two. This is the how-do-I-bind-a-variable-in-list problem. There are a few solutions, but the one I like, that involves no PLSQL is:

with id_generator
    as
    (
      SELECT regexp_substr(:txt, '[^,]+', 1, LEVEL) token
      FROM dual
      CONNECT BY LEVEL <= length(:txt) - length(REPLACE(:txt, ',', '')) + 1
    )
    select u.id, u.username
    from users u, id_generator g
    where u.id = g.token;

Bind your comma separated string as the value for :txt, and then structure your query as a join.

Full explanation - http://betteratoracle.com/posts/20-how-do-i-bind-a-variable-in-list

Stephen ODonnell
  • 4,441
  • 17
  • 19
0

This may help - pas any valid list of deptnos lk 10,20...:

Select * From scott.emp 
 Where deptno IN (&deptno)
/

If passing a string then use quotes ...IN ('&ename') Thanks.

Art
  • 7
  • 2