OracleParameter and IN Clause

Question

Is there a way to add a parameter to an IN clause using System.Data.OracleClient.

For example:

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
OracleCommand command = new OracleCommand(query, conn);
command.Parameters.Add(":Pram", OracleType.VarChar).Value = "'Ben', 'Sam'";

IMHO Any solution where a hard-coded "," character is part of the solution is not valid. Oracle should be generating this not the application. This also applies to any sort of string delimiter ' " — John Smith, Apr 07 '16 at 17:31

score 16 · Answer 1 · answered Nov 03 '10 at 10:52

You can wrap it in OracleCommandExtension 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;
    }
}

In year 2016, managed .NET clients to Oracle still cannot pass array parameter into `in`. Only oracle native client can. — Dzmitry Lahoda, Aug 10 '16 at 16:55
This will add 'n' parameters and each different arity will be a different query and will not be able to use the cached query plans. — Robert Taylor, Nov 28 '16 at 15:04
In the year 2019, Oracle still hasn't come to terms with the fact that arrays exist. Anyway... this worked for me, but I had to change `OracleType` to `OracleDbType` — , Aug 01 '19 at 16:59

score 12 · Answer 2 · answered Feb 12 '09 at 15:12

12

You can do it more easily with ODP.NET:

Create a TABLE type in your database:

CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);

Create a collection parameter:

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

Fill the parameter:
```
param = new string[2] {"Ben", "Sam" };
```

Bind the parameter to the following query:

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

answered Feb 12 '09 at 15:12

Quassnoi

413,100
91
616
614

2

It doesnot work for me, i keep getting ORA-00936: missing expression – surya Feb 11 '14 at 15:19
5

That's because you need to write "WHERE UserName IN (SELECT column_value FROM TABLE(CAST(:param AS t_varchar2)))", but then you'll probably get "ORA-01484: arrays can only be bound to PL/SQL statements" which suggests that such code should be placed in PL/SQL statements, not C# code – denis-bu Apr 03 '14 at 14:07
3

This approach requires database rights to modify schema - which is not my case. – Dzmitry Lahoda Aug 10 '16 at 16:58
If you do not have any rights to modify the schema, check [this](https://stackoverflow.com/a/48541060/1025320) answer below. – Calango Jun 08 '23 at 15:13

score 6 · Answer 3 · answered Jan 31 '18 at 11:28

6

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 the contents of the XMLTABLE could be a single parameter. Hence it should be usable from any language.

answered Jan 31 '18 at 11:28

user2192239

87
1
2

Thank you for this approach. This worked like a charm for what I needed and without needing any extra schema privileges. – Calango Jun 08 '23 at 15:12

score 5 · Answer 4 · edited May 23 '17 at 12:32

5

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

I would do something like this - please excuse the crude psudo 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 each of these are bound separately.

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

edited May 23 '17 at 12:32

Community

1
1

answered Sep 23 '10 at 21:35

Adam Butler

3,023
5
35
40

1

I think, it will be Sql injection – Hong Van Vit Oct 31 '17 at 09:22
I believe you are mistaken? how would you inject into this? – Adam Butler Nov 10 '17 at 01:55

score 3 · Answer 5 · edited May 23 '17 at 12:32

You can use an Oracle custom data type similar 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 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 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 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)))

Edgar · Answer 6 · 2015-07-28T05:50:37.060

Old question but I would like to share my code. Just a simple method to create a string that you can concatenate to a dynamic generated sql, without loosing the performance and 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 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 + " )  ";

score 1 · Answer 7 · answered Feb 12 '09 at 14:33

That way your query will be:

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

Those two names will be input as one single value.

Have a look at this thread from asktom.oracle.com 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

namford · Answer 8 · 2016-06-28T11:49:29.547

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

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

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

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

You must add each value separately. Something like this (writing on a Mac, so I couldn't 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);

score 0 · Answer 9 · answered Oct 13 '15 at 09:06

0

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

answered Oct 13 '15 at 09:06

raduV

1

score 0 · Answer 10 · answered Jun 16 '20 at 19:18

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

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

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

The example code would 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 the binding of the parameters, the Oracle command must be instructed to do the "bindery" by names.

oraCommando.BindByName = True

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

score -1 · Answer 11 · answered Aug 05 '15 at 08:01

Its very simple in ORACLE.

following steps:

1.create default type in oracle

CREATE OR REPLACE TYPE t_varchar_tab AS TABLE OF VARCHAR2(4000);

2.create function in oracle for seperating given string like "a,b,c" into ''a','b','c''

CREATE OR REPLACE FUNCTION in_list(p_in_list  IN  VARCHAR2)ETURNt_varchar_tab

AS

  l_tab   t_varchar_tab := t_varchar_tab();

  l_text  VARCHAR2(32767) := p_in_list || ',' ;

  l_idx   NUMBER;

BEGIN

  LOOP

    l_idx := INSTR(l_text, ',');

    EXIT WHEN NVL(l_idx, 0) = 0;

    l_tab.extend;

    l_tab(l_tab.last) := TRIM(SUBSTR(l_text, 1, l_idx - 1));

    l_text := SUBSTR(l_text, l_idx + 1);

  END LOOP;


  RETURN l_tab;

END;

3:Then use following query to extract data from table

SELECT * FROM TABLE_NAME EMP WHERE  IN (SELECT * FROM TABLE(in_list(i_input1)));

4.Input parameter passing from c#.net to oracle SP like

 cmd.Parameters.Add("i_input1", OracleType.VarChar, 50).Value = "S1,S2";

score -1 · Answer 12 · answered Apr 07 '16 at 17:33

-1

The solution should not contain the comma character nor single quotes, double quotes. I suggest that you use a temp table and then select from that. Populate the temp table using regular command parameters.

answered Apr 07 '16 at 17:33

John Smith

591
4
15

score -2 · Answer 13 · answered Aug 28 '09 at 13:53

-2

Actually, I would also try this code :

string query = "SELECT * FROM TableName WHERE UserName IN (:Pram)";
param = new string[2] {"Ben", "Sam" };
OracleCommand command = new OracleCommand(query, conn);
command.ArrayBindCount = param.Length;
command.Parameters.Add(":Pram", OracleType.VarChar).Value = param;

answered Aug 28 '09 at 13:53

Big

9
6

This solution doesn't work for me. It is equivalent to: "SELECT * FROM TableName WHERE UserName IN ("Ben")" - only first element in table is taken – Bartosz Pierzchlewicz May 25 '11 at 06:59

OracleParameter and IN Clause

13 Answers13

Linked