Click here to Skip to main content
15,792,380 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Team,
I have an Oracle select query with IN clause which needs to be pass ids with parameterized

string ids ="1,2,3,4,5";
string query="select * from employee where empid in (" + ids + " and ROWNUM <=10)";

What I have tried:

string ids ="1,2,3,4,5";
string query="select * from employee where empid in (" + ids + " and ROWNUM <=10)";
OracleCommand cmd = new OracleCommand(query, con);
string[] lstIds = ids.Split(",").Select(s => (s).ToArray();
stringBuilder sb = new StringBuilder();
string strParameter = string.empty;
string Param = "Param";
for(int i = 0; i <= lstIds.Length - 1; i++)
    strParameter += i == 0 ? Param + i : ",:" + Param + i;
    if (i == 0)
        sb.Append("," + lstIds[1]);

cmd.Parameters.Add(Param, sb.ToString());

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds= new DataSet();

Could you please assist on this how to use parameterised query with In clause
Updated 8-Oct-20 6:30am

1 solution

Basically speaking, you can't: Using comma separated value parameter strings in SQL IN clauses[^] explains why.

It also explains how it is possible in SqlServer by passing the list of IDs as a string via a query parameter, and then converting that to a temporary table which the IN clause can then reference. But I have no idea how that function could work in Oracle - I don't use it. It might work. It might not!
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900