Cursor with variable

Question: I'm trying to use a variable in an IN CLAUSE.

Assumptions & 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
As
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
As
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
As
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.

Download CURSORWITHVARIABLE.pdf.html