1

This code works for a query parameter that is a single value:

public void GetById(long id)
{
  var sql = "SELECT * FROM table1 WHERE id = :id";
  using(var cmd = new OracleCommand(sql, oracleConnection)) {
    cmd.Parameters.Add(new OracleParameter("id", id));
    ...
  }
}

What's the equivalent for an array or list of values?

public void GetByIds(long[] ids)
{
  var sql = "SELECT * FROM table1 WHERE id IN (:ids)";
  using(var cmd = new OracleCommand(sql, oracleConnection)) {
    cmd.Parameters.Add(new OracleParameter("ids", /* ??? */));
    ...
  }
}

In case it's not clear, I'm looking for something that will work like this:

sql = "SELECT * FROM table1 WHERE id IN (" + string.Join(',', ids) + ")";

but as a clean parameterized query.

  • 1
    Similar questions:https://stackoverflow.com/questions/541466/oracleparameter-and-in-clause and https://stackoverflow.com/questions/6155146/problem-using-oracle-parameters-in-select-in . – Luke Woodward Jul 31 '19 at 19:51
  • @LukeWoodward Good work finding those - I would have linked them myself if my search had foudn them. But all of the answers are scarily complicated, a lot of them involve hacking up a dynamic sql string (the thing I'm trying to avoid) or modifying the database schema with new types and functions... can it be this hard to pass an array? –  Jul 31 '19 at 20:28
  • I've decided to use one of the answers from https://stackoverflow.com/questions/541466/oracleparameter-and-in-clause and I will now try to close this question as a duplicate of it. –  Aug 01 '19 at 17:01
  • Possible duplicate of [OracleParameter and IN Clause](https://stackoverflow.com/questions/541466/oracleparameter-and-in-clause) –  Aug 01 '19 at 17:02

1 Answers1

1

You coud send the list of parameters as string and use a regular expresion to separate the id's. Example:

select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual

So

sql = "SELECT * FROM table1 WHERE id IN ( 
 select regexp_substr(:ids,'[^,]+', 1, level) from dual
)";

How to split comma separated string and pass to IN clause of select statement

Lvcios
  • 49
  • 1
  • 6
  • 1
    I'm going to agree with a comment from one of the related questions: Any solution where a hard-coded "," character is part of the solution is not valid. Oracle should be generating this not the application. This also applies to any sort of string delimiter ' " –  Jul 31 '19 at 20:23
  • I like this answer. Yes, I would prefer to bind an array or List directly, but those solutions seem to require an Oracle TYPE, PL/SQL or Oracle's own (Java) libraries. Here I can bind a String once. However, the SQL above is incomplete. It should be `SELECT regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual CONNECT BY LEVEL <= regexp_count('SMITH,ALLEN,WARD,JONES','[^,]+')`. Which means you actually need to bind the String twice or the second regexp could be bound with array size. – lafual Mar 01 '23 at 16:53