[ACCEPTED]-OracleParameter and IN Clause-oracle

You can wrap it in OracleCommandExtension 1 method:

public static class OracleCommandExtension
{
    public static OracleCommand AddParameterCollection<TValue>(this OracleCommand command, string name, OracleType type, IEnumerable<TValue> collection)
    {
        var oraParams = new List<OracleParameter>();
        var counter = 0;
        var collectionParams = new StringBuilder(":");
        foreach (var obj in collection)
        {
            var param = name + counter;
            collectionParams.Append(param);
            collectionParams.Append(", :");
            oraParams.Add(new OracleParameter(param, type) { Value = obj });
            counter++;
        }
        collectionParams.Remove(collectionParams.Length - 3, 3);
        command.CommandText = command.CommandText.Replace(":" + name, collectionParams.ToString());
        command.Parameters.AddRange(oraParams.ToArray());
        return command;
    }
}

You can do it more easily with ODP.NET:

1. Create a 3 TABLE type in your database:

   CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);
   

2. Create a collection 2 parameter:

   OracleParameter param = new OracleParameter();
   param.OracleDbType = OracleDbType.Varchar2;
   param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
   

3. Fill the parameter:

   param = new string[2] {"Ben", "Sam" };
   

4. Bind the parameter 1 to the following query:

   SELECT * FROM TableName WHERE UserName IN (TABLE(CAST(:param AS t_varchar2)));
   

I know this was asked a while ago but not 5 a brilliant answer.

I would do something 4 like this - please excuse the crude psudo 3 code

string args[] = {'Ben', 'Sam'};
string bindList = "";
for(int ii=0;ii<args.count;++ii)
{
  if(ii == 0)
  {
   bindList += ":" + ii;
  }
  else
  {
   bindList += ",:" + ii;
  }
  OracleParameter param = new OracleParameter();
  param.dbType = types.varchar;
  param.value = args[ii];
  command.Parameters.Add(param);
}

query = "select * from TableName where username in(" + bindList + ")";

So then query ends up having in(:1,:2) and 2 each of these are bound separately.

There 1 is also a similar question here: Oracle/c#: How do i use bind variables with select statements to return multiple records?

Perhaps using a different approach

SELECT * FROM SCOTT.EMP WHERE EMPNO IN (SELECT TO_NUMBER(X.COLUMN_VALUE) FROM XMLTABLE('7788,7900') X);

or

SELECT * FROM SCOTT.EMP WHERE ENAME IN (SELECT X.COLUMN_VALUE.GETSTRINGVAL() FROM XMLTABLE('"SCOTT", "JAMES"') X);

Where 3 the contents of the XMLTABLE could be a 2 single parameter. Hence it should be usable 1 from any language.

You can use an Oracle custom data type similar 4 to here:
http://www.c-sharpcorner.com/code/2191/pass-collection-to-oracle-stored-procedure-from-net-layer.aspx

and here:
https://stackoverflow.com/a/31466114/1867157

First create a type in 3 Oracle and give it permissions:

CREATE TYPE MYSCHEMA.VARCHAR2_TAB_T AS TABLE OF VARCHAR2(4000);
GRANT EXECUTE ON MYSCHEMA.VARCHAR2_TAB_T TO MYROLE

Then create 2 2 classes:

StringListCustomType.cs

public class StringListCustomType : IOracleCustomType, INullable
{
    public const string Name = "MYSCHEMA.VARCHAR2_TAB_T";

    [OracleArrayMapping()]
    public string[] Array;

    #region IOracleCustomType
    public OracleUdtStatus[] StatusArray { get; set; }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
        StatusArray = (OracleUdtStatus[])objectStatusArray;
    }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array, StatusArray);
    }
    #endregion

    #region INullable
    public bool IsNull { get; set; }

    public static StringListCustomType Null
    {
        get
        {
            StringListCustomType obj = new StringListCustomType();
            obj.IsNull = true;
            return obj;
        }
    }
    #endregion
}

StringListCustomTypeFactory.cs

[OracleCustomTypeMapping(StringListCustomType.Name)]
public class StringListCustomTypeFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    #region IOracleCustomTypeFactory
    IOracleCustomType IOracleCustomTypeFactory.CreateObject()
    {
        return new StringListCustomType();
    }
    #endregion

    #region IOracleArrayTypeFactory
    Array IOracleArrayTypeFactory.CreateArray(int numElems)
    {
        return new string[numElems];
    }

    Array IOracleArrayTypeFactory.CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }
    #endregion
}

Then you can add a parameter like 1 this:

dbParameter = new OracleParameter();
dbParameter.ParameterName = "myparamname";
dbParameter.UdtTypeName = StringListCustomType.Name;
dbParameter.OracleDbType = OracleDbType.Array;

if (myarray != null)
{
    StringListCustomType newArray = new StringListCustomType();
    newArray.Array = myarray;
    dbParameter.Value
}
else
{
    dbParameter.Value = StringListCustomType.Null;
}

Your query would look like this:

SELECT * 
  FROM MYSCHEMA.MYTABLE 
 WHERE MYVARCHARFIELD IN (SELECT COLUMN_VALUE 
                            FROM TABLE(CAST(:myparamname AS MYSCHEMA.VARCHAR2_TAB_T)))

That way your query will be:

SELECT * FROM TableName WHERE UserName IN ('''Ben'', ''Sam''');

Those two names 3 will be input as one single value.

Have a 2 look at this thread from asktom.oracle.com 1 to find out how to get a dynamic in list.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:210612357425

Old question but I would like to share my 5 code. Just a simple method to create a string 4 that you can concatenate to a dynamic generated 3 sql, without loosing the performance and 2 security of bind parameters:

    /// <summary>
    /// 1 - Given an array of int, create one OracleParameter for each one and assigin value, unique named using uniqueParName
    /// 2 - Insert the OracleParameter created into the ref list.
    /// 3 - Return a string to be used to concatenate to the main SQL
    /// </summary>
    /// <param name="orclParameters"></param>
    /// <param name="lsIds"></param>
    /// <param name="uniqueParName"></param>
    /// <returns></returns>
    private static string InsertParameters(ref List<OracleParameter> orclParameters, int[] lsIds, string uniqueParName)
    {
        string strParametros = string.Empty;

        for (int i = 0; i <= lsIds.Length -1; i++)
        {
            strParametros += i == 0 ? ":" + uniqueParName + i : ", :" + uniqueParName + i;

            OracleParameter param = new OracleParameter(uniqueParName + i.ToString(), OracleType.Number);
            param.Value = lsIds[i];
            orclParameters.Add(param);
        }
        return strParametros;
    }

And use like 1 this:

List<OracleParameter> parameterList = new List<OracleParameter>();
int[] idAr = new int[] { 1, 2, 3, 4};
string idStr = InsertParameters(ref parameterList, idAr, "idTest");
string SQL = " SELECT name FROM tblTest WHERE idTest in ( " + idStr + " )  ";

I came across it when searching for the 9 same question, so I'd like to add an answer 8 that I found helpful since I don't believe 7 the above really achieve it:

http://forums.asp.net/t/1195359.aspx/1?Using%20bind%20variable%20with%20an%20IN%20clause

I'll add the 6 answer here as well in case the link becomes 5 invalid:

Re: Using bind variable with an 4 IN clause Dec 17, 2007 06:56 PM|LINK

You 3 must add each value separately. Something 2 like this (writing on a Mac, so I couldn't 1 test it)

string sql = "select id, client_id as ClientID, acct_nbr as AcctNbr from acct where acct_nbr in ( %params% )";
        OracleConnection conn = new OracleConnection(DBConnection);
        OracleCommand cmd = new OracleCommand();


        List<string> params=new List<string>();

        foreach(string acctNbr in AcctNbrs.Split(','))
        {
            string paramName=":acctNbr" + params.Count.Tostring();
            params.Add(paramName)
            OracleParameter parms = new OracleParameter(paramName, OracleType.VarChar);
            parms.Value = acctNbr;
            cmd.Parameters.Add(parms);

        }

        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql.Replace("%params%",params.ToArray().Join(","));
        cmd.Connection = conn;

        OracleDataAdapter da = new OracleDataAdapter(cmd);
        da.Fill(ds);

SELECT * FROM Clients 
WHERE id IN ( 
SELECT trim(regexp_substr(str, '[^,]+', 1, level)) strRows 
FROM (SELECT :Pram as str from dual ) t   
CONNECT BY instr(str, ',', 1, level -1) >0);

0

Although the question is old, I explain 26 the way in which I solved it in my case. The 25 example is in Vb.NET but I think it is equally 24 understood. The solution, in general lines, was 23 to convert the IN statement into a series 22 of OR conditions with their respective parameters, all 21 by program.

Starting from having a string 20 with the searched values, separated by commas, WITHOUT 19 the string quotes that Oracle would use 18 and assuming that you have a defined OracleCommand, which 17 I called oraCommando in the example. What 16 I did was assemble the query string by splitting 15 the string that has the searched values, creating 14 as many OR comparisons as necessary and 13 assigning them value with their respective 12 parameters. Special attention should be 11 paid in assigning the name of the parameter 10 in the query string assembly so as not to 9 leave spaces between the name and the number 8 that is put at the end so that they are 7 all different names.

strCommand & = " UserName = :userName" & puntParam & " "

The example code would 6 be:

dim param as string = "Ben, Sam"
dim strCommand as string = "SELECT * FROM TableName WHERE"
dim puntParam as integer = 0
for each paramAnali as string in split (param, ",")
    puntParam + = 1
    if puntParam> 1 then
        strCommand & = "or"
    end if
    strCommand & = "UserName =: userName" & puntParam.ToString () & ""

    Dim paramNew As New OracleParameter With {
      .ParameterName = "userName" & puntParam.ToString (),
      .OracleDbType = OracleDbType.Varchar2,
      .Direction = ParameterDirection.Input,
      .Value = Trim (paramAnali)}

    oraCommando.Parameters.Add (paramNew)

next

Also, in order not to have problems with 5 the binding of the parameters, the Oracle 4 command must be instructed to do the "bindery" by 3 names.

oraCommando.BindByName = True

In this way, the query automatically 2 adjusts to the number of values ​​received 1 without the need to adjust the code.