Oracle: How can I parameterize a list?

I have a query with a where clause that looks like:

WHERE field IN ( 1, 2, 3 )

Is possible to parametrize this so it looks something like:

WHERE field in ( :list )?

How do开发者_开发百科 you create the :list parameter, assuming it's a list of ints, that could be anywhere from 1 to 10 ints?

ASP.net, webforms if that makes a difference.

There are two ways of accommodating Dynamic IN lists:

1. Convert the comma separated list into a derived table (AKA inline view)
2. Use dynamic SQL

Non-Dynamic

Most prefer the non-dynamic SQL approach - this link provides various ways to do it. The biggest reason to use this over:

WHERE :list LIKE '%,' || t.column || ',%'

...is that the above:

1. Will never be able to use an index
2. Won't be able to match the first or last parameter in the list, because commas are required on either end to match

The simple fact is, it won't work as intended. A regex, supported on Oracle 10g+, would allow for conditional checking on the column but still faces the problem of rendering an index as moot

Dynamic SQL

Mention "dynamic SQL", and you likely will be hounded about SQL injection attacks. Using a bind variable alleviates the concern.

That said, dynamic SQL requires the least change to the query.

Same answer as SQL Server, already asked here: Parameterize an SQL IN clause

As far as I know, you cannot. You'd be mixing values and operators in the same place.

However, it should be fairly simple to create automatically an array of parameters in ASP.NET and get this dynamically:

WHERE field IN (:list1, :list2, :list3, :list4)

This question 'how do I bind an in list' seems to come up a lot.

The easiest 'trick' to do this in Oracle is using the answer I posted here:

Dynamic query with HibernateCritera API & Oracle - performance

I cannot pretent it is my trick, as it came from Tom Kyte's blog, who is an Oracle guru!