I can't use IN clause in a package with variables

I need to use this code in a PL/SQL package:

Var_All Varchar2(100) := '''var1'',''var2''';

Select ... many fields ...
Bulk Collect Into ResultTable
From ... many tables with joins
Where my_table.MC01 IN (Var_All);

As far as I know, I cannot use the variable Var_All with an IN clause because the data is lost due to the apostrophes. The variables var1, var2, varN are strings that actually only contain numbers, so it would also be good if I could compare the value of the my_table.MC01 field with numbers.

I found two solutions, but I don't like either of them.

Dynamic SQL
Cursor, where I make some of these changes:

Instead of IN clause: Where my.table.MC01 = record.var;

And instead of "Bulk Collect Into", I would call a separate procedure where I put the data into the ResultTable.

Is there a better solution?

Solution

A bind variable is a single scalar value - it is not a list. Therefore when you pass in a string containing 'var1','var2' you are passing a single value and not a list of two values so if you had a my_table.MC01 that was 'var1','var2' then it would be matched but var1 or var2 would not be matched.

You can either continue passing a single string an match using LIKE to perform a sub-string match (ensuring that you match complete terms with the surrounding delimiters):

DECLARE
   Var_All     Varchar2(100) := '''var1'',''var2''';
   ResultTable your_collection_data_type;
BEGIN
   SELECT many_fields
   BULK COLLECT INTO ResultTable
   FROM   my_table -- many tables with joins
   WHERE  ',' || Var_All || ',' LIKE '%,''' || my_table.MC01 || ''',%';
END;
/

or you can use a collection:

CREATE TYPE string_list IS TABLE OF VARCHAR2(20);

Then use it in the query:

DECLARE
   Var_All     string_list := string_list('var1', 'var2');
   ResultTable your_collection_data_type;
BEGIN
   SELECT many_fields
   BULK COLLECT INTO ResultTable
   FROM   my_table -- many tables with joins
   WHERE  my_table.MC01 MEMBER OF Var_All;
END;
/

or:

DECLARE
   Var_All     string_list := string_list('var1', 'var2');
   ResultTable your_collection_data_type;
BEGIN
   SELECT many_fields
   BULK COLLECT INTO ResultTable
   FROM   my_table -- many tables with joins
   WHERE  my_table.MC01 IN (SELECT COLUMN_VALUE FROM TABLE(Var_All));
END;
/

or using a JOIN (which may give different results if the input collection contains duplicates):

DECLARE
   Var_All     string_list := string_list('var1', 'var2');
   ResultTable your_collection_data_type;
BEGIN
   SELECT many_fields
   BULK COLLECT INTO ResultTable
   FROM   my_table -- many tables with joins
          INNER JOIN TABLE(Var_All) v
          ON v.COLUMN_VALUE = my_table.MC01;
END;
/