Array selects

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
AL
Posts: 4
Joined: Wed 12 Apr 2006 13:46

Array selects

Post by AL » Wed 12 Apr 2006 13:54

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...

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 13 Apr 2006 09:54

The multiple inserts/updates/deletes intend execution of the same command several times with different parameters. Selects in this case would not have any sense.

AL
Posts: 4
Joined: Wed 12 Apr 2006 13:46

ok

Post by AL » Thu 13 Apr 2006 12:24

Ok, the question then is how to bind array to variable list in IN operator.

Any suggestions?

AL
Posts: 4
Joined: Wed 12 Apr 2006 13:46

Post by AL » Fri 14 Apr 2006 16:54

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...
}

Post Reply