Oracle Parameters with IN statement?

by

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

More Related Contents:

  1. problem using Oracle parameters in SELECT IN
  2. How to execute an .SQL script file using c#
  3. Difference between Parameters.Add(string, object) and Parameters.AddWithValue
  4. C# constructing parameter query SQL – LIKE %
  5. Search SQL by date ASP.NET
  6. I want to insert a record in DB and then need to return a row
  7. How can I add user-supplied input to an SQL statement?
  8. How to get last inserted id?
  9. What is the syntax for an inner join in LINQ to SQL?
  10. LINQ to Entities does not recognize the method ‘System.String ToString()’ method, and this method cannot be translated into a store expression
  11. Object cannot be cast from DBNull to other types
  12. C# SQL Server – Passing a list to a stored procedure
  13. linq case statement
  14. How can I log the generated SQL from DbContext.SaveChanges() in my Program? [duplicate]
  15. Default parameter for value must be a compile time constant?
  16. c# Using Parameters.AddWithValue in SqlDataAdapter
  17. SQL WHERE clause matching values with trailing spaces
  18. Equivalent of SQL ISNULL in LINQ?
  19. SqlBulkCopy from a List
  20. How to fill Dataset with multiple tables?
  21. SqlConnection.Close() inside using statement
  22. OracleParameter and IN Clause
  23. Passing a SQL parameter to an IN() clause using typed datasets in .NET
  24. ASP.NET ODBC Query with parameters
  25. The variable name ‘@’ has already been declared. Variable names must be unique within a query batch or stored procedure
  26. C# params object[] strange behavior
  27. Check if a SQL table exists
  28. C#: Oracle Data Type Equivalence with OracleDbType
  29. Mapping columns in a DataTable to a SQL table with SqlBulkCopy
  30. SQL connection string for microsoft access 2010 .accdb

Leave a Comment