Got a c#.net app which I need to modify. The query at the moment effectively does this:
select * from contract where contractnum = :ContractNum
(very simplified, just to show we're using an = and one parameter)
That parameter is read in from the Settings.Settings file on the C# app and has one string in it. I need to modify it to include multiple contracts, so I figure I can change the SQL to:
select * from contract where contractnum in (:ContractNum)
but that returns no results, no matter how I format the string in the parameter.
Is there a way I can get oracle to do an IN with a parameter?
Have yet to find a db that supports evaluating a single string variable containing commas to separate as the sole IN clause.
Your options are to substring the variable so the comma delimited variable contents are turned into rows, so you can then join onto this. Or to use dynamic SQL, which is a SQL statement constructed as a string in a sproc before the statement is executed.