Multiple Values To Oracle Parametter Object (IN)

  • Thread starter Thread starter timburda
  • Start date Start date
T

timburda

Here is the scenario:

I am looking to execute the following query:

SELECT *
FROM TBL_USER
WHERE USER_ID IN ( 1,2,3,4)

Now, since in-line SQL is rightfully frowed upon, I am attempting to
use parameterized SQL, but I don't know how to create an
OracleParameter which can be used in an "IN" situation.

So, my code looks something like this:

string sql = "SELECT *
FROM TBL_USER
WHERE USER_ID IN (:USERID)"

//Not sure if the parameter type should be varchar2 or int - neither
seems to work
OracleParameter param = new OracleParameter("USERID","1,2,3,4")

whenever I execute the command to fill the dataset, I get an error from
the Oracle provider

Thoughts anyone?

thanks -

Tim Burda
 
Oracle 8i or later? Consider a stored proc with a varchar param and
formatting the string as XML. You can then parse the XML using the Oracle
XML bits. With Oracle's ODP.NET, you also have the option of table
variables, but I have not played with it.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
 
Greg (or should I say Cowboy?) :

Thanks for your reponse.

I am on Oracle 10.2

The reason I chose in-line SQL for this particular problem was because
the query have several optional parameters to the WHERE clause (for
example, user id, last name, first name, ssn, etc may or may not have
been speicfied).

I have never a good strategy for how to make this work in a stored
proc. Maybe the XML technique you mentioned will work for this case.

If you have any snippets on how to implment this, I'd love to see the
samples.

Thanks -

Tim
 
I, unfortunately, do not have Oracle samples, as I have not been on Oracle
in awhile. The 1.1 bits were very lacking and while the 2.0 bits are better,
the Oracle implementations are better.

There are basically three options with multiple values in Oracle.

1. Rip the string apart. I forget the syntax. In SQL it is split.
2. Use XML
3. Use a result set type value, like a REF CURSOR or table

The third option is harder with .NET, due to the differences between .NET
and Oracle. XML is my favorite, largely because you can do more than a
simple where clause using XML. It may be overkill in some situations,
however.

I wish I remember the Oracle split. In SQL Server, you do a kind of WHERE IN
with the SPLIT(stringName, ',') to check multiple values.

Okay, so there is one other option. Dynamically build the SQL String in the
sproc.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************
 
Back
Top