Oracle Parameters with IN statement?

Question

Got a c#.net app which I need to modify. The query at the moment effectively does this:

select * from contract where contractnum = :ContractNum

(very simplified, just to show we're using an = and one parameter)

That parameter is read in from the Settings.Settings file on the C# app and has one string in it. I need to modify it to include multiple contracts, so I figure I can change the SQL to:

select * from contract where contractnum in (:ContractNum)

but that returns no results, no matter how I format the string in the parameter.

Is there a way I can get oracle to do an IN with a parameter?

Answer 1

You can use an Oracle collection of numbers as a parameter (bind variable) when you use ODP.NET as dataprovider. This works with Oracle server 9, 10 or 11 and ODP.net release >= 11.1.0.6.20 .

A similar solution is possible when you use Devart's .NET dataprovider for Oracle.

Let's select the contracts with contractnum's 3 and 4.

We have to use an Oracle type to transfer an array of contract numbers to our query.

MDSYS.SDO_ELEM_INFO_ARRAY is used because if we use this already predefined Oracle type we don't have to define our own Oracle type. You can fill MDSYS.SDO_ELEM_INFO_ARRAY with max 1048576 numbers.

using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

[OracleCustomTypeMappingAttribute("MDSYS.SDO_ELEM_INFO_ARRAY")]
public class NumberArrayFactory : IOracleArrayTypeFactory
{
  public Array CreateArray(int numElems)
  {
    return new Decimal[numElems];
  }

  public Array CreateStatusArray(int numElems)
  {
    return null;
  }
}

private void Test()
{
  OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
  b.UserID = "sna";
  b.Password = "sna";
  b.DataSource = "ora11";
  using (OracleConnection conn = new OracleConnection(b.ToString()))
  {
    conn.Open();
    using (OracleCommand comm = conn.CreateCommand())
    {
      comm.CommandText =
      @" select  /*+ cardinality(tab 10) */ c.*  " +
      @" from contract c, table(:1) tab " +
      @" where c.contractnum = tab.column_value";

      OracleParameter p = new OracleParameter();
      p.OracleDbType = OracleDbType.Array;
      p.Direction = ParameterDirection.Input;
      p.UdtTypeName = "MDSYS.SDO_ELEM_INFO_ARRAY";
      //select contract 3 and 4
      p.Value = new Decimal[] { 3, 4 };
      comm.Parameters.Add(p);

      int numContracts = 0;
      using (OracleDataReader reader = comm.ExecuteReader())
      {
        while (reader.Read())
        {
           numContracts++;
        }
      }
      conn.Close();
    }
  }
}

The index on contract.contractnum isn't used when one omits hint /*+ cardinality(tab 10) */. I assumed contractnum is the primary key so this column will be indexed.

See also here: http://forums.oracle.com/forums/thread.jspa?messageID=3869879#3869879