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 — sstan
– sstan, Commented Oct 9, 2015 at 14:12
I think you can find answer here - asktom.oracle.com/pls/asktom/… — Tatiana
– Tatiana, Commented Oct 9, 2015 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
– Allan, Commented Oct 9, 2015 at 18:21

Allan · Accepted Answer · 2015-10-09 14:26:29Z

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 9, 2015 at 14:26

Allan

17.5k5 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

hc0re Over a year ago

Seems very nice, but I get Error(240,34): PLS-00642: local collection types not allowed in SQL statements...

Allan Over a year ago

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.

Community · Accepted Answer · 2017-05-23 10:27:04Z

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.

Sentinel · Accepted Answer · 2015-10-09 15:07:04Z

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 9, 2015 at 15:07

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related