2 Replies Latest reply: Mar 23, 2013 6:12 AM by be*447301*ll RSS

    Problem with binding parameter to in clause

    be*447301*ll
      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