CODEWITHSUNDEEP
c#.netoracle-database
rc.
rc.

Reputation: 281

OracleParameter and IN Clause

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'";

Upvotes: 24

Views: 44097

Answers (13)

Mariano Javier Scazzino
Mariano Javier Scazzino

Reputation: 1

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.

Upvotes: 0

user2192239
user2192239

Reputation: 97

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.

Upvotes: 6

namford
namford

Reputation: 1261

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

Upvotes: 0

DoubleJ
DoubleJ

Reputation: 569

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

Upvotes: 3

John Smith
John Smith

Reputation: 601

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.

Upvotes: -1

raduV
raduV

Reputation: 1

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

Upvotes: 0

Rohini
Rohini

Reputation: 7

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

Upvotes: -1

Edgar
Edgar

Reputation: 1137

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

Upvotes: 1

argy
argy

Reputation: 169

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

Upvotes: 16

Adam Butler
Adam Butler

Reputation: 3037

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?

Upvotes: 5

Big
Big

Reputation: 9

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;

Upvotes: -2

Quassnoi
Quassnoi

Reputation: 425291

You can do it more easily with ODP.NET:

  1. Create a TABLE type in your database:

    CREATE TYPE t_varchar2 AS TABLE OF VARCHAR2(4000);

  2. Create a collection 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 to the following query:

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

Upvotes: 12

Yasin B
Yasin B

Reputation: 387

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

Upvotes: 1

Related Questions