Oracle/PLSQL: Cursor with variable in an "IN CLAUSE"

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

Assumptions & declarations:

Ref_cursor is of type REF CURSOR declared in Package
I will to pass a comma separated Numbers as a string
This should be used in the query in the IN Clause
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.