Passing in Oracle Parameter to SQL string

Question

I'm having a problem where I don't know how I'm supposed to pass in an Oracle parameter where the C# type is a string and the Oracle type is a Varchar2.

Currently I'm passing in this string as CMS','ABC thinking that Oracle will add in the '' that surround this string making it a varchar2 that looks like 'CMS','ABC'.

This works for a single string like CMS but when the value is something longer, like something typically in a IN (list) command the parameter won't be passed in correctly.

This is the code I'm referring too.

string sql = 'SELECT name FROM Pers p WHERE p.FirstName IN (:names)';

The below works when the value of :names being passed in is CML without any quotes.

OracleParameter param = new OracleParameter(":names", OracleDbType.Varchar2, "CML", ParameterDirection.Input);

Below doesn't work when the value of :names being passed in is CML','ABC with quotes on the inside.

OracleParameter param = new OracleParameter(":names", OracleDbType.Varchar2, "CML','ABC", ParameterDirection.Input);

Why is that?

Does Oracle add in single quotes around the parameter when it's passed into the sql statement? Why doesn't it add quotes around the second case?

Answer 1

From your comments/answers I was able to come up with this solution. I hope it helps others who come.

To get around ODT.NET parameters not working with multiple comma separated values you can divide each value into its own parameter. Like the following.

string allParams = "CML, ABC, DEF";
string formattedParams = allParams.Replace(" ", string.Empty); // Or a custom format
string [] splitParams = formattedParams.Split(',');

List<OracleParamter> parameters = new List<OracleParameter>();

string sql = @"SELECT * FROM FooTable WHERE FooValue IN (";
for(int i = 0; i < splitParams.Length; i++)
{
    sql += @":FooParam" + i + ",";
    parameters.Add(new OracleParameter(":FooParam" + i, OracleDbType.Varchar2, splitParams[i], ParameterDirection.Input));
{
sql = sql.Substring(0, (sql.Length - 1));
sql += ')';

The string sql will now have this as it's value: SELECT * FROM FooTable WHERE FooValue IN (:FooParam0,:fooParam1, etc...)

This will solve the problem.

Another approach would be to add in a bunch of OR clauses for each parameter. The above example is better since you don't write a bunch of OR clauses though.