This discussion is archived
2 Replies Latest reply: Mar 23, 2013 4:12 AM by be*447301*ll RSS

Problem with binding parameter to in clause

be*447301*ll Newbie
Currently Being Moderated
I'm trying to use a query with a parametrized in clause. On StackOverflow I found the following answer which I followed:
http://stackoverflow.com/questions/6155146/problem-using-oracle-parameters-in-select-in?lq=1
Unfortunately the query fails with ORA-00936: Ausdruck fehlt (missing expression)

Here's the code I'm using:
private void Test()
{
    var ocsb = new Oracle.DataAccess.Client.OracleConnectionStringBuilder();
    ocsb.DataSource = "//myServer/myDb";
    ocsb.UserID = "myUserId";
    ocsb.Password = ocsb.UserID;

    try
    {
        using (var connection = new Oracle.DataAccess.Client.OracleConnection())
        using (var command = connection.CreateCommand())
        {
            connection.ConnectionString = ocsb.ConnectionString;
            connection.Open();

            command.CommandText = "CREATE OR REPLACE TYPE array_varchar2 AS TABLE OF VARCHAR2(50)";
            command.ExecuteNonQuery();

            command.CommandText = @"
SELECT 
  *
FROM 
  t_gebaeude 
WHERE 
  id_guid IN (TABLE(CAST(:id_guid AS array_varchar2)))
";
            var parameter = new Oracle.DataAccess.Client.OracleParameter();
            parameter.ParameterName = "id_guid";
            parameter.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
            parameter.OracleDbType = Oracle.DataAccess.Client.OracleDbType.Varchar2;
            parameter.Value = new String[] { "860C452751DD4A89952F998E8B9D573C", "B1E0672D20374603BF5EA649D6F7E0D8" };

            command.Parameters.Add(parameter);
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    System.Diagnostics.Debug.WriteLine(reader["id_guid"].ToString());
                    System.Diagnostics.Debug.WriteLine(reader["bezeichnung"].ToString());
                }
            }
        }
    }
    catch (Exception ex)
    {
        System.Diagnostics.Debug.WriteLine(ex.Message);
    }
}
ExecuteNonQuery() works, so the connection is okay. "SELECT * from t_gebaeude" without where-clause also works. The field id_guid is defined as varchar2(50).
Oracle version is 10.2.0.4.0, Oracle.DataAccess.dll version is 2.112.3.0.

Any help would be really appreciated, thanks,
Bernd

Edited by: be**** on 15.02.2013 15:25

Edited by: be**** on 18.02.2013 08:14, added version info

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points