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

Question

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.

Answer 1

If you want to use the TABLE function, then you must create the TYPE as an OBJECT type which will store it in the database and create a function using it. Later you could use the table function in your PL/SQL code.

Otherwise, the way you are doing, using a NESTED TABLE you will have to LOOP again to reference the collection object.

SET serveroutput ON
DECLARE
type str_typ
IS
  TABLE OF VARCHAR2(200);
  str_sub str_typ := str_typ ();
BEGIN
  SELECT DISTINCT TABLE_NAME BULK COLLECT INTO str_sub FROM ALL_TAB_COLUMNS;
  FOR i IN 1..str_sub.count
  LOOP
    dbms_output.put_line(str_sub(i));
  END LOOP;
END;
/

So, you cannot use it as a database object, for that you must create the type as an object type, and create a table function.

For example,

Create the type

SQL> CREATE TYPE col_type AS OBJECT (
  2    col_name VARCHAR2(50)
  3  );
  4  /

Type created.

SQL> CREATE TYPE col_sub_type IS TABLE OF col_type;
  2  /

Type created.

Build the table function

SQL> CREATE OR REPLACE FUNCTION get_col_tab RETURN col_sub_type AS
  2    l_type  col_sub_type := col_sub_type();
  3  BEGIN
  4    FOR i IN (SELECT DISTINCT TABLE_NAME t_name FROM ALL_TAB_COLUMNS) LOOP
  5      l_type.extend;
  6      l_type(l_type.last) := col_type(i.t_name);
  7    END LOOP;
  8
  9    RETURN l_type;
 10  END;
 11  /

Function created.

Test it

SELECT *
FROM   TABLE(get_col_tab());