Binding Variable For IN Queries

While Using Binding Variable its not getting the Values

eg,, SELECT * FROM EMP WHERE USER IN (:VALUES开发者_StackOverflow中文版)

IF i use this above query its not executed

passing the values directly

SELECT * FROM EMP WHERE USER IN (+ arraylist +) its working fine

========================================

here is the sample caode

string[] Myarray = ListVal.split(','); Query = "SELECT * FROM EMP WHERE USER IN (:VALUES)"

OracleParameter Param = { new OracleParameter (":VALUES",OracleDBType.Array)

};

Param[0].value = Myarray;

using(dr = OracleFactory.ExecuteReader(CommandType.Text,Query,true,Param)) {

}

while Executing this I get an Exception Like

Invalid Parameter Binding ParameterName VALUES

---

You need to pass the values as an array and to slightly modify the query. The following code is for ODP.NET. I don't know whether it can also be done with Microsoft's deprecated Oracle drivers.

At first, you need to define a table type (e.g. for VARCHAR):

CREATE TYPE varchar_table AS TABLE OF VARCHAR2(2000); 

When you create the parameter for the query, declare it as an associative PL/SQL array:

OracleParameter param1 = new OracleParameter(); 
param1.OracleDbType = OracleDbType.Varchar2; 
param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray; 

Then assign some values:

param1 = new string[2] { "johnp", "billt" }; 

And your query needs a cast:

SELECT * FROM EMP WHERE USER IN (TABLE(CAST(:values AS varchar_table)))

---

Unfortunately you cannot pass arrays to bind variables.

Your only options are directly inserting a comma separated list in the query or writing your own helper/wrapper that automatically rewrites your statement to

IN (:value1, :value2, :value3...)

and fills those values automatically. This could at least in some cases (if you have lots of repeat queries with the same amount of parameters) ease up the work for the Oracle parser.

---

First of all, I seriously doubt the query is not executed.

Or rather, if it really was not executed, then you would get an exception, and you would ask about that.

What I think you're saying is that you get no results back, and that's different.

The reason for this is that you can't parameterize IN-clauses in most databases.

What end up being done is that you're doing an IN-clause with just 1 value. Exactly what that value is depends on the collection you pass in and how that can be transformed into an appropriate type for the query engine. It might be that you end up calling .ToString() on the collection and stuffing that in there.

In any case, it can't be done. You have to go with the string concatenation way.

Or, you could create your SQL in such a way that you add enough parameters inside the IN-clause to have 1 value per parameter.

In other words, you would do this for a 4-element collection:

SELECT * FROM EMP WHERE USER IN (:V1, :V2, :V3, :V4)

And then bind each parameter to one of the elements of the collection. You still have to build the SQL dynamically though.

---

(Also cross-posted to)

I know this is an old question but it is one of several in which the selected answer did not solve my problem and I don't want to start yet another thread on this topic so I'll just put down what I found in my travels in the hope that it might help someone. This is a repeat answer because I'm not sure which is more likely to come up if someone is looking for a solution like this.

I don't work with Oracle much but, like in SQL Server, it seems that to pass a table-valued parameter you need to have a corresponding UDT (user defined table) to which you have EXECUTE permissions (I could be wrong). This means that other answers suggesting the use of a built-in SYS UDT come with some freight and I couldn't figure out whether it really is possible to pass a table to something that is not a PL/SQL stored procedure in the current version of ODP.net.

Second, the string-parse solution is a kludge for all the obvious reasons (can't cache the execution plan or whatever Oracle calls it, doesn't scale well, etc).

So I spent rather a lot of time trying do the IN-clause using a table-valued parameter on a datamart to which I have only READ permission before I was hit by a blinding flash of the obvious (at an ASP.net forum no less). Turns out Oracle supports Xml queries 'natively' so instead of passing an array of values you can pass an xml list (if that is all you need). Again, I may be wrong, but it gets handled as a legitimate bind parameter and this is an example of how simple it is to use (vb.net, ADO.net, ODP.net using NuGet package):

Dim xe As New XElement("l", New XElement("i", "ITEM-A"), New XElement("i", "ITEM-B"))
Using conn As New OracleConnection(myConnectionString)
    conn.Open()
    Using cmd As OracleCommand = conn.CreateCommand()
        cmd.CommandType = CommandType.Text
        Dim query As String
        query = "  SELECT s.FOO, q.BAR " & vbCrLf
        query &= " FROM TABLE1 s LEFT OUTER JOIN " & vbCrLf
        query &= "      TABLE2 q ON q.ID = s.ID " & vbCrLf
        query &= " WHERE (COALESCE(q.ID, 'NULL') NOT LIKE '%OPTIONAL%') AND "
        query &= "       (s.ID IN ("
        query &= "                      SELECT stid "
        query &= "                      FROM XMLTable('/l/i' PASSING XMLTYPE(:stid) COLUMNS stid VARCHAR(32) PATH '.')"
        query &= "                 )"
        query &= "        )"
        cmd.CommandText = query
        Dim parameter As OracleParameter = cmd.Parameters.Add("stid", OracleDbType.NVarchar2, 4000)
        parameter.Value = xe.ToString
        Using r As OracleDataReader = cmd.ExecuteReader
            While r.Read()
                //Do something
            End While
        End Using
    End Using
    conn.Close()

This is more of an observation than a carefully researched solution so please comment if there is anything inappropriate about doing it this way.

EDIT. There is apparently a 4000 character limit using this method (2000 if NVARCHAR) so I had to watch my paging. The informative error message you get if you go over is 'ORA-01460: unimplemented or unreasonable conversion requested'