---

Oracle / PLSQL: Cursor with variable in an IN CLAUSE

Question: I'm trying to use a variable in an IN CLAUSE of a cursor. Here are my assumptions and declarations.

1. Ref_cursor is of type REF CURSOR declared in Package
2. I will to pass a comma separated Numbers as a string
3. This should be used in the query in the IN clause
4. Execute the Query and Return the Output as REF Cursor

Something similar to the following:

CREATE OR REPLACE FUNCTION func_name (inNumbers in Varchar2)
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN
   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (inNumbers);

   RETURN out_cursor;
END;

I seem to be getting an error when I try the code above. How can I use a variable in an IN CLAUSE?

Answer: Unfortunately, there is no easy way to use a variable in an IN CLAUSE if the variable contains a list of items. We can, however, suggest two alternative options:

Option #1

Instead of creating a string variable that contains a list of numbers, you could try storing each value in a separate variable. For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;
   v1 varchar(2);
   v2 varchar(2);
   v3 varchar(2);

BEGIN

   v1 := '1';
   v2 := '2';
   v3 := '3';

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (v1, v2, v3);

   RETURN out_cursor;

END;

Option #2

You could try storing your values in a table. Then use a sub-select to retrieve the values.

For example:

CREATE OR REPLACE FUNCTION func_name
   RETURN PackageName.ref_cursor
IS
   out_cursor PackageName.Ref_cursor;

BEGIN

   OPEN out_cursor
   FOR SELECT * FROM Table_name
   WHERE column_name IN (SELECT values FROM list_table);

   RETURN out_cursor;

END;

In this example, we've stored our list in a table called list_table.