CODEWITHSUNDEEP
oracle-databaseplsql
hc0re
hc0re

Reputation: 1986

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

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!

Upvotes: 3

Views: 1565

Answers (3)

Sentinel
Sentinel

Reputation: 6449

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

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

Upvotes: 0

Allan
Allan

Reputation: 17429

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.

Upvotes: 3

Caleb Mauer
Caleb Mauer

Reputation: 672

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.

Upvotes: 0

Related Questions