Oracle parameter and IN clause in SSRS(RDL) reports

Question

Before marked as duplicate, I have read the following:

• Oracle "IN clause" from parameter
• Parameterize an SQL IN clause
• problem using Oracle parameters in SELECT IN

Supposed I have this query on my DataSource in my .rdl report published in our report server:

SELECT ...
FROM ...
WHERE c.cluster_cd IN (:paramClusterCD)

Report Builder 2.0 automatically recognized a parameter as @paramClusterCD. On my wpf project, I have to create a parameter with multiple values like this:

var arrCluster = (lbCluster.SelectedItems.Cast<CLUSTER_MSTR>().ToList()).Select(x => x.CLUSTER_CD).ToArray();
string strCluster = string.Join(",", arrCluster); // result is "1,2,3"

Now whenever I run(pass the parameter in the report viewer), I have this error:

ORA-01722: invalid number

Workaround from the previous post won't work since this is a SSRS report.

Answer 1

It's not going to work this way, because Oracle won't recognize that you're actually trying to pass in a list of possible values.

What you want is a query like

select * from t where x in (1,2,3)

but what your code does is

select * from t where x = '1,2,3'

As x is numeric, Oracle tries to cast '1,2,3' into a number - and fails...

Please refer to this excellent thread at AskTom for correct solutions (and a sermon about the importance of bind variables).

Update: Tom's first answer already contains everything you need, but it used the now obsolete THE keyword instead of TABLE. So here are the steps that should work for you:

first create a type for a collection of numbers

create or replace type TableOfNumber as table of number;

then create a function that splits your string and returns your newly created collection

create or replace function in_list( p_string in varchar2 ) return TableOfNumber as
  l_string        long default p_string || ',';
  l_data          TableOfNumber := TableOfNumber();
  n               number;
begin
  loop
    exit when l_string is null;
    n := instr( l_string, ',' );
    l_data.extend;
    l_data(l_data.count) := to_number( substr( l_string, 1, n-1 ) );
    l_string := substr( l_string, n+1 );
  end loop;

  return l_data;
end;

Now you can use this function in a query:

SELECT ...
FROM ...
WHERE c.cluster_cd IN 
 (select * from TABLE (select cast(in_list(:paramClusterCD) as mytableType) from dual))

Answer 2

Kindly try the below if you can ensure that the parameters passed is a number and if c.cluster_cd is a number column

SELECT ...
FROM ...
WHERE to_char(c.cluster_cd) IN ((:paramClusterCD));