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?

Solution 1

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.

Solution 2

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

Solution 3

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

Comments

[Gareth]

I found a reference to using & instead of : for parameter identification and that works: Parameter value: '1182411', '1182423' SQL: select * from contract where contractnum in (&ContractNum) I have no idea why this works, or whether it is "officially" supported by Orace or just TOAD. Have you used & for Oracle before? Any idea what the difference is?

[OMG Ponies]

:variable is a bind variable; the only time I recall using &variable was to define/use a bind variable within PLSQL Developer.

[APC]

The ampersand is the default character indicating a substitution variable in SQLPlus. TOAD (and other IDEs) support some SQLPlus syntax.

[dpbradley]

+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

[Vincent Malgrat]

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

[tuinstoel]

Your options are certainly options but they are not the only one.

[tuinstoel]

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.

[TTT]

Read also: blog.tanelpoder.com/2012/08/02/…

[Carl]

This worked for our scenario. The gotcha for me was including the NumberArrayFactory, even though it is not explicitly used in the code.

[Carl]

This is the approach that we have been using for almost 10 years. After (finally) moving over to use ODP.NET, we are being forced to use the method highlighted in the answer posted by tuinstoel. That seems to be a much nicer solution anyway, rather than string splitting.

[Vincent Malgrat]

@Carl I agree: a collection would be a better choice in general. It might take a bit more effort to setup, but a collection doesn't suffer from the limitations of varchars (max length, no type check on each member of the list, hard to use the separator character in a member, etc...)

[Chris Schaller]

You really should use code fencing and some other formatting in your post, it is hard to read as a single continuous line like that.