SELECT ... WHERE ... IN ... using parametrized query (odp.net) RSS

• • Reply
  

  johram

  All-Star

  28531 Points

  3567 Posts

  SELECT ... WHERE ... IN ... using parametrized query (odp.net)

  Apr 06, 2010 09:30 AM|LINK

  I am trying to do a SELECT ... WHERE ... IN construct, using a parametrized query with Oracle (odp.net) and I cannot make it work correctly.

  OracleCommand cmd = new OracleCommand("SELECT * FROM SomeTable WHERE STATUS IN (:STATUS)", conn);
  OracleParameter param = cmd.CreateParameter();
  param.ParameterName = "STATUS";
  param.Direction = ParameterDirection.Input;
  param.OracleDbType = OracleDbType.Decimal;
  param.Value = "1,5,8";
  cmd.Parameters.Add(param);

  The STATUS column is NUMBER(2) in the database. My problem seems to be related to getting the correct OracleDbType. I've tried Varchar2, Long, Decimal... nothing works. Should I go for the ArrayBindSize construct?

  If this post was useful to you, please mark it as answer. Thank you!
  • Edited by johram on Apr 06, 2010 09:31 AM
  • Edited by johram on Apr 06, 2010 11:05 AM
• • Reply
  

  davidelcam

  Member

  2 Points

  1 Post

  Re: SELECT IN ... using parameter (odp.net)

  Apr 06, 2010 10:05 AM|LINK

  You can try with this

   

  OracleCommand cmd = new OracleCommand(
  "SELECT * FROM SomeTable WHERE STATUS IN (
      SELECT item.value FROM
          XMLTable('/list/item'
          PASSING XMLTYPE(:STATUS)
          COLUMNS value     NUMBER(2)    PATH '/item/.') item)", conn);
  OracleParameter param = cmd.CreateParameter();
  param.ParameterName = "STATUS";
  param.Direction = ParameterDirection.Input;
  param.OracleDbType = OracleDbType.Varchar2;
  param.Value = "<list><item>1</item><item>5</item><item>8</item></list>";
  cmd.Parameters.Add(param);

  
   

   

   

• • Reply
  

  CruzerB

  Contributor

  5399 Points

  1098 Posts

  Re: SELECT IN ... using parameter (odp.net)

  Apr 06, 2010 10:20 AM|LINK

  Dear johram,

  
  

  OracleCommand cmd = new OracleCommand("SELECT * FROM SomeTable WHERE STATUS IN (:STATUS1,:STATUS2,:STATUS3)", conn);
              OracleParameter param = cmd.CreateParameter();
              param.ParameterName = "STATUS1";
              param.Direction = ParameterDirection.Input;
              param.OracleDbType = OracleDbType.Decimal;
              param.Value = 1;
              cmd.Parameters.Add(param);
              param.ParameterName = "STATUS2";
              param.Direction = ParameterDirection.Input;
              param.OracleDbType = OracleDbType.Decimal;
              param.Value = 5;
              cmd.Parameters.Add(param);
              param.ParameterName = "STATUS3";
              param.Direction = ParameterDirection.Input;
              param.OracleDbType = OracleDbType.Decimal;
              param.Value = 8;
              cmd.Parameters.Add(param);

  
  U may try this. And 1 thing to mention, since u already set the type to Decimal, then u cannot use double quote for param.Value = "1,5,8"; 

  else it will be in string type.
  

  No matter how much time you spend on coding. Wish you happy coding.
  My Technical Blog
• • Reply
  

  johram

  All-Star

  28531 Points

  3567 Posts

  Re: SELECT ... WHERE ... IN ... using parametrized query (odp.net)

  Apr 06, 2010 11:16 AM|LINK

  @CruzerB:  This could be a solution if there was a fixed number of parameters. In my case, however I may get any number of "status parameters"...

  @davidelcam: XML... creative solution :-) However, this does not run very well on my dev machine with Oracle XE. Seems to be some Java-dependency (XQuery?) thingie that does not play along. Dunno if this can easily be fixed though. Also, is this something that is supported out of the box on Oracle 11?

  If this post was useful to you, please mark it as answer. Thank you!
• • Reply
  

  CruzerB

  Contributor

  5399 Points

  1098 Posts

  Re: SELECT ... WHERE ... IN ... using parametrized query (odp.net)

  Apr 06, 2010 11:42 AM|LINK

  Dear johram,

  I do practice to generate unknow size of parameter in runtime. Below is part of the code:

  sql = "UPDATE CONSUMER SET STATUS=:STA WHERE ";
  
  
  foreach (string acc in accountList) {
  	sqlaccountlist = sqlaccountlist + ":ACCNUM" + count.ToString + ", ";
  	count = count + 1;
  }
  
  
  if (count > 1) {
  	sqlaccountlist = Left(sqlaccountlist, sqlaccountlist.Length - 2);
  	sqlaccountlist = " ACCNUM IN (" + sqlaccountlist + ") ";
  	sql = sql + sqlaccountlist;
  }
  
  
  .
  .
  .
  .
  .
  
  
  for (i = 1; i <= count; i++) {
  	MyDBCommand.Parameters.Add("ACCNUM" + i.ToString, OracleDbType.Varchar2).Value = accountList(i - 1);
  }

  
  
  

  
  

  No matter how much time you spend on coding. Wish you happy coding.
  My Technical Blog
  • Marked as answer by johram on Apr 06, 2010 12:22 PM
• • Reply
  

  a_programmer

  Member

  2 Points

  1 Post

  Re: SELECT ... WHERE ... IN ... using parametrized query (odp.net)

  Apr 07, 2010 07:05 AM|LINK

  try this:

  select * from someTable where InStr(:AStatus,to_char(STATUS)) > 0 

• • Reply
  

  Coffee7

  Member

  2 Points

  3 Posts

  Re: SELECT ... WHERE ... IN ... using parametrized query (odp.net)

  Nov 21, 2011 03:24 PM|LINK

  <div>

  Hi, Can you tell me if you found an answer to your post. I am looking to do something similar, but it doesn't work for me either. I am hoping since yours is an earlier post you might have found a way to do this.

  I tried using the ArrayBindSize construct, however the query is only return results for the first value in the array.

  Thanks!!

  </div>