CODEWITHSUNDEEP
asp.netsqloracle-databaseparameters
chris
chris

Reputation: 37490

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.

Upvotes: 3

Views: 1023

Answers (4)

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

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!

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332781

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.

Upvotes: 2

Álvaro González
Álvaro González

Reputation: 146660

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)

Upvotes: 0

Nick Craver
Nick Craver

Reputation: 630627

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

Upvotes: 0

Related Questions