PL/SQL - Bind Variable and In Comparison Operators

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

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

3 - Example

3.1 - 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
Advertising

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

3.3 - 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
lang/plsql/bin_variable_in.txt · Last modified: 2017/09/06 22:18 by gerardnico