How to use a string as a variable in NOT IN clause?

Question

I will give You two slices of code from the SQL script I'm currently working with, but they will suffice. Firstly, I am declaring a variable:

FUNCTION Run
( i_PlafId  IN INTEGER
)
RETURN INTEGER
IS

l_tables_excl VARCHAR2(256) := 'TABLE_1,TABLE_2';

Later I would like to use it in some place, like this:

AND cos.table_name NOT IN l_tables_excl

Which would be the exact representation of:

AND cos.table_name NOT IN ('TABLE_1', 'TABLE_2')

There is no way of experimenting, cause a run of this Oracle package takes about 2 days...

Thanks in advance!

Answer 1

The best way to deal with a list of values like this is to use an array:

create or replace type t_table_list as table of varchar2(50);

FUNCTION Run
( i_PlafId  IN INTEGER
)
RETURN INTEGER
IS

l_tables_excl t_table_list := t_table_list('TABLE_1','TABLE_2');
...
AND cos.table_name NOT IN (select * from table(l_tables_excl))

You should note that the type must be created as a database object, not declared within a package.