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:
IS section of a function:
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
AND col.table_name NOT IN (SELECT * FROM TABLE (l_tabellen_excl))
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.