Oracle/c#: How do i use bind variables with select statements to return multiple records?

Question

I have a question regarding Oracle bind variables and select statements.

What I would like to achieve is do a select on a number different values for the primary key. I would like to pass these values via an array using bind values.

select * from tb_customers where cust_id = :1

int[] cust_id = { 11, 23, 31, 44 , 51 };

I then bind a DataReader to get the values into a table.

The problem is that the resulting table only contains a single record (for cust_id=51). Thus it seems that each statement is executed independently (as it should), but I would like the results to be available as a collective (single table).

A workaround is to create a temporary table, insert all the values of cust_id and then do a join against tb_customers. The problem with this approach is that I would require temporary tables for every different type of primary key, as I would like to use this against a number of tables (some even have combined primary keys).

Is there anything I am missing?

Answer 1

Not asking the question as to why you would want to do this to begin with. Shouldn't the sql statement be something like

select * from tb_customers where cust_id = 11 or 23 or ...

Edit:

I am limited in Oracle but when I look at the documentation I think that you might have to do something like this:

variable i number
exec :i := 11
select * from tb_customers where cust_id = :i

This would allow you to take advantage of binding. You will have to add each record return to your own collection since it will still only return one at a time.

Answer 2

I know this was asked a while ago but not a brilliant answer.

I would do something like this - please excuse the crude psudo code

string bindList = "";
for(int ii=0;ii<cust_id.count;++ii)
{
  if(ii == 0)
  {
   bindList += ":" + ii;
  }
  else
  {
   bindList += ",:" + ii;
  }
  OracleParameter param = new OracleParameter();
  param.dbType = types.int;
  param.value = cust_id[ii];
  command.Parameters.Add(param);
}

query = "select * from tb_customers where cust_id in(" + bindList + ")";

So then query ends up having in(:1,:2,:3,etc) and each of these are bound separately.

There is also a similar question here: http://stackoverflow.com/questions/541466/oracleparameter-and-in-clause