PL/SQL - Bind Variable and In Comparison Operators

About

With a bind variable, the value is passed atomically.

If you pass the value 1, 2 in a IN comparison operator, PL/SQL will try to find the value 1, 2 and not the value 1 and the value 2.

Articles Related

Example

With one value

With one value, the bind variable will do its works.

DECLARE
  TYPE recId IS RECORD ( id NUMBER);
  TYPE typeCursor IS REF CURSOR;
  cur   typeCursor;
  curRow recId;
  sql_stmt varchar2(400);
BEGIN
   sql_stmt := '
   select * from
   (
   select 1 as id from dual
   union all
   select 2 from dual
   )
   where
   id in (:1)';
   OPEN cur FOR sql_stmt USING '1'; -- I want id in 1 
   LOOP
      FETCH cur INTO curRow;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Id: ' || curRow.id);
   END LOOP;
   CLOSE cur;
END;
/

anonymous block completed
Id: 1

With two values

But with two values, the bind variable mechanism will consider that it's one value and will return no value

DECLARE
  TYPE recId IS RECORD ( id NUMBER);
  TYPE typeCursor IS REF CURSOR;
  cur   typeCursor;
  curRow recId;
  sql_stmt varchar2(400);
BEGIN
   sql_stmt := '
   select * from
   (
   select 1 as id from dual
   union all
   select 2 from dual
   )
   where
   id in (:1)';
   OPEN cur FOR sql_stmt USING '1,2'; -- I want id in 1 and in 2
   LOOP
      FETCH cur INTO curRow;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Id: ' || curRow.id);
   END LOOP;
   CLOSE cur;
END;
/

anonymous block completed

Solution

One solution for now, is:

to create the dynamic sql statement with two binds variables
or to create the statement dynamically with concatenation and without bind variable.

DECLARE
  TYPE recId IS RECORD ( id NUMBER);
  TYPE typeCursor IS REF CURSOR;
  cur   typeCursor;
  curRow recId;
  sql_stmt varchar2(400);
BEGIN
   sql_stmt := '
   select * from
   (
   select 1 as id from dual
   union all
   select 2 from dual
   )
   where
   id in (:1' || ',:2)';
   OPEN cur FOR sql_stmt USING '1','2';
   LOOP
      FETCH cur INTO curRow;
      EXIT WHEN cur%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('Id: ' || curRow.id);
   END LOOP;
   CLOSE cur;
END;
/

anonymous block completed
Id: 1
Id: 2