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

Question

User1191518856 posted

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?

Answer 1

User322528513 posted

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); 

 

 

 

Answer 2

User614805505 posted

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.

Answer 3

User1191518856 posted

@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?

Answer 4

User614805505 posted

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);
}

Answer 5

User1083145879 posted

try this:

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

Answer 6

User-302760439 posted

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!!