Oracleparameter and in Clause

Oracle IN clause from parameter

I believe there is a 'better way', but I'm not sure what it is right now...

This should work for you though:

replace:

b.customer_no in (p_Scope);

with

instr(p_Scope, ','||b.customer_no||',' ) > 0

This will search p_Scope and return a value of > 0 if b.customer_no appears in the list.

Make sure that the first and last character in the list is a comma (',')

(also, as a new comer to Oracle I found Tech Republic to be a very helpful quick resource.)

problem using Oracle parameters in SELECT IN

To pass a set of values, you need to use Oracle's table or array types.

At first, you create a table type (e.g. for NUMBER):

CREATE TYPE number_table AS TABLE OF NUMBER; 

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

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

Then assign some values:

param1 = new int[] { 3857, 3858, 3863, 3285 }; 

And your query needs a cast:

SELECT * FROM tablename a 
where a.flokkurid in (TABLE(CAST(:manyNumbers AS number_table)))
order by sjodategund, rodun 

Passing in Oracle Parameter to SQL string

From your comments/answers I was able to come up with this solution. I hope it helps others who come.

To get around ODT.NET parameters not working with multiple comma separated values you can divide each value into its own parameter. Like the following.

string allParams = "CML, ABC, DEF";
string formattedParams = allParams.Replace(" ", string.Empty); // Or a custom format
string [] splitParams = formattedParams.Split(',');

List<OracleParamter> parameters = new List<OracleParameter>();

string sql = @"SELECT * FROM FooTable WHERE FooValue IN (";
for(int i = 0; i < splitParams.Length; i++)
{
    sql += @":FooParam" + i + ",";
    parameters.Add(new OracleParameter(":FooParam" + i, OracleDbType.Varchar2, splitParams[i], ParameterDirection.Input));
{
sql = sql.Substring(0, (sql.Length - 1));
sql += ')';

The string sql will now have this as it's value: SELECT * FROM FooTable WHERE FooValue IN (:FooParam0,:fooParam1, etc...)

This will solve the problem.

Another approach would be to add in a bunch of OR clauses for each parameter. The above example is better since you don't write a bunch of OR clauses though.

Oracle parameterized SQL using IN clause with multiple values not working

You are passing a single string argument in to IN(). If that happens to contain a single number then you're effectively doing:

where RID in ('12345')

which is processed with an implicit conversion, since your RID column is numeric, as:

where RID in (to_number('12345'))

which is fine. But with multiple values in a single string argument you're really trying to do:

where RID in (to_number('12345,5566,8899'))

and to_number('12345,5566,8899') will throw ORA-01722: invalid number.

There are various ways to unpack a delimited string into individual values but a simple one is by treating them as an XPath sequence and putting them through an XMLTable call:

sbQuery.Append("where RID in (select RID from XMLTable(:rid columns RID number path '.'))");

As a demo of that approach, first how the XMLTable call expands the string using a SQL*Plus bind variable:

var rid varchar2(30);

exec :rd := '12345,5566,8899';

select RID from XMLTable('12345,5566,8899' columns RID number path '.');

       RID
----------
     12345
      5566
      8899

and then in a dummy query against a dummy table:

with EXC_LOG (RID, SUPV_EMP_ID, STATUS, SUPV_COMMENT) as (
            select 12345, 123, 'OK', 'Blah blah' from dual
  union all select 8899, 234, 'Failed', 'Some comment' from dual
  union all select 99999, 456, 'Active', 'Workign on it' from dual
)
select *
from EXC_LOG
where RID in (select RID from XMLTable('12345,5566,8899' columns RID number path '.'));

       RID SUPV_EMP_ID STATUS SUPV_COMMENT 
---------- ----------- ------ -------------
     12345         123 OK     Blah blah    
      8899         234 Failed Some comment 

Your code will just do an update instead of a select, using the same filter.

Oracle Parameters with IN statement?

Have yet to find a db that supports evaluating a single string variable containing commas to separate as the sole IN clause.

Your options are to substring the variable so the comma delimited variable contents are turned into rows, so you can then join onto this. Or to use dynamic SQL, which is a SQL statement constructed as a string in a sproc before the statement is executed.

Changing the where clause to use Oracle Command Parameter

You are making multiple mistakes in your code. I'm writing code for you, but remaining you have to fix.

        string jrs = "";
        string dateofBirth = "";
        string connectionString = ConfigurationManager.ConnectionStrings["TGSDataConnection"].ConnectionString;
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            string query = "SELECT * from LIMS_SAMPLE_RESULTS_VW where JRS_NO =:jrs and DOB=:dateofBirth";
            OracleCommand command = new OracleCommand(query, connection);
            command.Parameters.Add(new OracleParameter("jrs", jrs));
            command.Parameters.Add(new OracleParameter("dateofBirth", dateofBirth));
            command.CommandType = CommandType.Text;
            connection.Open();
            OracleDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    string value = reader["ColumName"].ToString();
                }
            }
            finally
            {
                reader.Close();
            }
        }

Do not write query in code, Write stored procedure and then call it by code.
You have to use ExecuteReader to get the result from SELECT query.
Replace ColumName with your column name in table.
Do not use @ with arguments, use: before them.
Check your connection string whether is it correct or not.
You can run your query separately in Oracle DB just to test whether your query is giving the required results or not.
Check the DataType of jrs and dateOfBirth, In my example I have taken as string.
Close Reader in finally block.
My personal opinion, do not use SELECT *, always use column names. because it will give you all columns, may be you require only 2 or 3.

Oracle In-Clause Parameter Padding

There's a tradeoff:

If you choose, say, 5^x, then for 7 parameters your IN-list will have 25 members, instead of just 8. The query will then take longer to run - the fact that the tail values are all equal won't help.

Note that your example of an explain plan for the IN-list of (1,2,3,3) is irrelevant. That has hard-coded values, not bind variables. The relevant example is (:bind1, :bind2, :bind3, :bind4); when the query is parsed, the optimizer can't assume that :bind3 will always equal :bind4 (for the obvious reason that that's not even true in general).

2^x is usually a good tradeoff between "how many hard parses to allow" and "how fast the queries will be". Otherwise you could just use a single query, with 1000 parameters (the max allowed) - why even have more than ONE such query?

Oracle parameter and IN clause in SSRS(RDL) reports

It's not going to work this way, because Oracle won't recognize that you're actually trying to pass in a list of possible values.

What you want is a query like

select * from t where x in (1,2,3)

but what your code does is

select * from t where x = '1,2,3'

As x is numeric, Oracle tries to cast '1,2,3' into a number - and fails...

Please refer to this excellent thread at AskTom for correct solutions (and a sermon about the importance of bind variables).

Update: Tom's first answer already contains everything you need, but it used the now obsolete THE keyword instead of TABLE. So here are the steps that should work for you:

first create a type for a collection of numbers

create or replace type TableOfNumber as table of number;

then create a function that splits your string and returns your newly created collection

create or replace function in_list( p_string in varchar2 ) return TableOfNumber as
  l_string        long default p_string || ',';
  l_data          TableOfNumber := TableOfNumber();
  n               number;
begin
  loop
    exit when l_string is null;
    n := instr( l_string, ',' );
    l_data.extend;
    l_data(l_data.count) := to_number( substr( l_string, 1, n-1 ) );
    l_string := substr( l_string, n+1 );
  end loop;

  return l_data;
end;

Now you can use this function in a query:

SELECT ...
FROM ...
WHERE c.cluster_cd IN 
 (select * from TABLE (select cast(in_list(:paramClusterCD) as mytableType) from dual))

---

Related Topics