Looking for a datatype which can be filled with BULK COLLECT INTO and then to be selected from with SELECT

I have a great problem with an PL-SQL package I’m currently working with.

All I want to do is to create a small piece of code which will do this:

In the IS section of a function:

l_tabellen_excl DBMS_utility.name_array;

Later in the code:

SELECT DISTINCT TABLE_NAME
BULK COLLECT INTO l_tabellen_excl
FROM
ALL_TAB_COLUMNS
WHERE
TABLE_NAME IN ('TAB_1', 'TAB_2');

To finally use this variable in a SELECT statement:

AND col.table_name NOT IN (SELECT * FROM TABLE (l_tabellen_excl))

I get ORA-22905: cannot access rows from a non-nested table item here.

I know that I could just write AND col.table_name NOT IN ('TAB_1','TAB_2') but I don’t want to hardcode it in the deepest pits of the code… making it hard to find and less configurable.

I tried a TON of things:

type array_t is table of varchar2(10);

Doesn’t work. I get an error saying that I can’t use a locally declared collection in an SELECT statement.

I tried to cast the variable i_tabellen_excl on a locally declared type – like a workaround. But I get ORA-00902 – invalid datatype.

I Tried to declare a VARCHAR2 containing a string with comma separated table list, it seems to be working but still it is far away from a clean, good written, well designed code.

I tried other options not worth mentioning, like trying to write a function etc.

I’m lost in this matter, ANY ideas would be great to test out.


Source: oracle

Leave a Reply