Oracle Parameters with in Statement

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.

How to use parameters in a 'where value in...' clause?

Using dynamic SQL is the simplest approach from a coding standpoint. The problem with dynamic SQL, though, is that you have to hard parse every distinct version of the query which not only has the potential of taxing your CPU but has the potential to flood your shared pool with lots of non-sharable SQL statements, pushing out statements you'd like to cache, causing more hard parses and shared pool fragmentation errors. If you're running this once a day, that's probably not a major concern. If hundreds of people are executing it thousands of times a day, that is likely a major concern.

An example of the dynamic SQL approach

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  varchar2(100) := '10,20';
  3    l_rc       sys_refcursor;
  4    l_dept_rec dept%rowtype;
  5  begin
  6    open l_rc for 'select * from dept where deptno in (' || l_deptnos || ')';
  7    loop
  8      fetch l_rc into l_dept_rec;
  9      exit when l_rc%notfound;
 10      dbms_output.put_line( l_dept_rec.dname );
 11    end loop;
 12    close l_rc;
 13* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

Alternately, you can use a collection. This has the advantage of generating a single, sharable cursor so you don't have to worry about hard parsing or flooding the shared pool. But it probably requires a bit more code. The simplest way to deal with collections

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos  tbl_deptnos := tbl_deptnos(10,20);
  3  begin
  4    for i in (select *
  5                from dept
  6               where deptno in (select column_value
  7                                  from table(l_deptnos)))
  8    loop
  9      dbms_output.put_line( i.dname );
 10    end loop;
 11* end;
SQL> /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

If, on the other hand, you really have to start with a comma-separated list of values, then you will have to parse that string into a collection before you can use it. There are various ways to parse a delimited string-- my personal favorite is to use regular expressions in a hierarchical query but you could certainly write a procedural approach as well

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_deptnos     tbl_deptnos;
  3    l_deptno_str  varchar2(100) := '10,20';
  4  begin
  5    select regexp_substr(l_deptno_str, '[^,]+', 1, LEVEL)
  6      bulk collect into l_deptnos
  7      from dual
  8   connect by level <= length(replace (l_deptno_str, ',', NULL));
  9    for i in (select *
 10                from dept
 11               where deptno in (select column_value
 12                                  from table(l_deptnos)))
 13    loop
 14      dbms_output.put_line( i.dname );
 15    end loop;
 16* end;
 17  /
ACCOUNTING
RESEARCH

PL/SQL procedure successfully completed.

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

Oracle PL/SQL - Receive a list of values as IN parameter, use them in the WHERE clause and return the results in a procedure

an alternative solution is to use the collections:

First you have to create a global collection as a type

CREATE OR REPLACE TYPE tab_number is TABLE OF NUMBER;

you can then use it as a type of your arguments in your procedure

CREATE PROCEDURE YOUR_PROC_NAME (
    in_id_list IN            tab_number ,
    C_RESULTS     OUT           SYS_REFCURSOR
) IS
BEGIN
    OPEN C_RESULTS FOR 
    SELECT *
      FROM YOUR_TABLE
     WHERE YOUR_COLUMN IN (
              SELECT column_value from table(in_id_list)
       );

END;

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.

---

Related Topics