Hi, exploring array bind feature of OraDirect, I noticed that all examples are about inserts/updates/deletes... And nothing about selects.
But the problem is simple. I need to pass array of IDs (guids in my case, raw(16)) to retrieve several rows.
So select looks like this:
select * from objects where guid=:guid
I define "guid" parameter and pass one value. So this select works just fine. But the question is what happens if I put an array of elements in the value of this parameter?
Will this works like:
select * from objects where guid in (x1, x2, x3...xN)
where x1...xN are values from my array?
Any help would be really appreciated...
Array selects
found a solution...
select * from table where id in (select * from table(:param))
where param is plsql type "NumTable" (defined as table of number).
OracleParameter param = cmd.Parameters.Add("param", OracleDbType.Array);
OracleArray values = new OracleArray("NumTable", connection);
values.Add(value1);
values.Add(value2);
...
values.Add(valueX);
param.Value = values;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
.. read your data here...
}
select * from table where id in (select * from table(:param))
where param is plsql type "NumTable" (defined as table of number).
OracleParameter param = cmd.Parameters.Add("param", OracleDbType.Array);
OracleArray values = new OracleArray("NumTable", connection);
values.Add(value1);
values.Add(value2);
...
values.Add(valueX);
param.Value = values;
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
.. read your data here...
}