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!

Possible duplicate of [Declaration of multiple values in Oracle BIND Variables](http://stackoverflow.com/questions/6632741/declaration-of-multiple-values-in-oracle-bind-variables) — sstan, Oct 09 '15 at 14:12
I think you can find answer here - https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:210612357425 — Tatiana, Oct 09 '15 at 14:15
The suggested duplicate question pertains to getting a list via a bind variable, in this case, it's a static list inside of PL/SQL which makes user-defined type more practical. — Allan, Oct 09 '15 at 18:21

score 3 · Accepted Answer · answered Oct 09 '15 at 14:26

3

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.

answered Oct 09 '15 at 14:26

Allan

17,141
4
52
69

Seems very nice, but I get `Error(240,34): PLS-00642: local collection types not allowed in SQL statements`... – hc0re Oct 09 '15 at 15:11
The type has to be created as a database object in order to be used in SQL. That means it must have its own `create or replace`, rather than just being declared in PL/SQL. If you can see the type in ALL_TYPES, then it's been created the right way. – Allan Oct 09 '15 at 18:18

score 0 · Answer 2 · edited May 23 '17 at 10:27

Option A - Use LIKE

You can do a LIKE to see if the table name is in the string. Whether or not this works will depend on how similar your table names are.

/* Returns true if table name isn't a substring of l_tables.excl */
AND l_tables_excl NOT LIKE '%' || cos.table_name || '%'

Option B - Split string into table

Or you can split the string into a table and you'll be able to use NOT IN. It's a bit more difficult so I'll just give you references:

Testing note: If running your package takes two days, you might want to find a way of only doing a part of the work. Like if it processes a 1000 rows, add a variable to tell it to only do 100 rows just so it will finish. You really need to be able to test and two days is just too long.

score 0 · Answer 3 · answered Oct 09 '15 at 15:07

0

You can use the REGEXP_LIKE function to simulate the in clause:

WHERE NOT REGEXP_LIKE(l_tables_excl, '(^|,)'||cos.table_name||'(,|$)')

answered Oct 09 '15 at 15:07

Sentinel

6,379
1
18
23

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

3 Answers3