views:

1140

answers:

3

Hi Guys,

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?

any help appreciated, thanks all.

+1  A: 

Have yet to find a db that supports evaluating a single string variable containing commas to separate as the sole IN clause.

Your options are to substring the variable so the comma delimited variable contents are turned into rows, so you can then join onto this. Or to use dynamic SQL, which is a SQL statement constructed as a string in a sproc before the statement is executed.

OMG Ponies
Gareth
OMG Ponies
The ampersand is the default character indicating a substitution variable in SQL*Plus. TOAD (and other IDEs) support some SQL*Plus syntax.
APC
The ampersand will likely not work with C#. Even if it does work it won't be able to bind (performance problem) and will be open to SQL injection since it will build a query on the fly (security problem).
Vincent Malgrat
Your options are certainly options but they are not the only one.
tuinstoel
+4  A: 

Hi Gareth,

you could use a pipelined function to transform a string into a table which could be used with the IN operator. For example (tested with 10gR2):

SQL> select * from table(demo_pkg.string_to_tab('i,j,k'));

COLUMN_VALUE
-----------------
i
j
k

with the following package:

SQL> CREATE OR REPLACE PACKAGE demo_pkg IS
  2     TYPE varchar_tab IS TABLE OF VARCHAR2(4000);
  3     FUNCTION string_to_tab(p_string VARCHAR2,
  4                            p_delimiter VARCHAR2 DEFAULT ',')
  5        RETURN varchar_tab PIPELINED;
  6  END demo_pkg;
  7  /

Package created
SQL> CREATE OR REPLACE PACKAGE BODY demo_pkg IS
  2     FUNCTION string_to_tab(p_string VARCHAR2,
  3                            p_delimiter VARCHAR2 DEFAULT ',')
  4        RETURN varchar_tab PIPELINED IS
  5        l_string          VARCHAR2(4000) := p_string;
  6        l_first_delimiter NUMBER := instr(p_string, p_delimiter);
  7     BEGIN
  8        LOOP
  9           IF nvl(l_first_delimiter,0) = 0 THEN
 10              PIPE ROW(l_string);
 11              RETURN;
 12           END IF;
 13           PIPE ROW(substr(l_string, 1, l_first_delimiter - 1));
 14           l_string          := substr(l_string, l_first_delimiter + 1);
 15           l_first_delimiter := instr(l_string, p_delimiter);
 16        END LOOP;
 17     END;
 18  END demo_pkg;
 19  /

Package body created

Your query would look like this:

select * 
  from contract 
 where contractnum in (select column_value
                         from table(demo_pkg.string_to_tab(:ContractNum)))
Vincent Malgrat
+1 - AFAIK, this is the only way to use all of: bind variable, unknown number of elements, and the "IN" clause. If you have a known upper bound on the number of elements you can always code the statement to use that number of elements and programatically substitute nulls when there are leftover placeholders
dpbradley
No this is not the only way to use bind variables. You can also bind a Oracle collection of numbers and join with table(:numbers). You will no longer need a pipelined function. However your data provider has to support it.
tuinstoel
+1  A: 

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

tuinstoel