Fill up schema-level table, and use it with join

Question

I have a schema-level record type and a schema-level table type, and I want to fill this table with records.

create or replace Type t_SLRekord As Object(OWNER VARCHAR2(128),  
                                            TABLE_NAME VARCHAR2(128));

create or replace Type t_SLTable Is Table of t_SLRekord;

This doesn't work I know, because the record "fields" cannot be accessed this way:

Declare
    my_SLTable t_SLTable := t_SLTable();
Begin 
    Select Owner, Table_Name
    Bulk Collect Into my_SLTable
    From ALL_TABLES
    Where Owner='OW1' And Table_Name Like 'TN_KO%';
End;

I should have a loop, from where I call a procedure which fills the table, like this:

my_SLTable.EXTEND(1);
my_SLTable(my_SLTable.Count).OWNER := rec(i).OWNER;
my_SLTable(my_SLTable.Count).TABLE_NAME := rec(i).TABLE_NAME;

Later I want to make a join with this schema-level table. How can I do that?

Select t.column_value.Table_Name, at.Owner, at.Table_Name
    Bulk Collect Into v_AT_Table -- just an other PL/SQL coollection
    From ALL_TABLES at, table(my_SLTable) t
    Right Join table(my_SLTable) t on t.column_value.Table_Name = at.Table_Name -- How can I build this join?
        And Owner = 'OW3';

Sorry for the "imaginary" code. The exact code would be quite long. My goal is to find which Oracle table is missing.

Answer 1

If you want to find the missing tables in the OW3 schema compared to the OW1 schema then you can use a single query and do not need to use PL/SQL or object- or collection-types.

SELECT Table_Name
FROM   ALL_TABLES
WHERE  table_name LIKE 'TN_KO%'
GROUP BY table_name
HAVING COUNT(CASE owner WHEN 'OW1' THEN 1 END) > 0
AND    COUNT(CASE owner WHEN 'OW3' THEN 1 END) = 0;

---

However, to solve your problem with the types, if you have the object- and collection-types:

CREATE TYPE t_SLRekord AS OBJECT(
  OWNER      VARCHAR2(128),  
  TABLE_NAME VARCHAR2(128)
);

CREATE TYPE t_SLTable IS TABLE OF t_SLRekord;

and the tables:

CREATE TABLE tn_ko_1 (id NUMBER);
CREATE TABLE tn_ko_2 (id NUMBER);

Simplifying your code to work on a single schema (you can expand it back to comparing multiple as you require), then it will work if you collect the object types into the collection (rather than trying to fit multiple columns into a collection) then when you use it in a table collection expression you can treat it as it it was another table and do not need to use COLUMN_VALUE, only the column identifiers:

DECLARE
  my_SLTable t_SLTable;
  v_AT_Table t_SLTable;
BEGIN
  SELECT t_SLRekord(Owner, Table_Name)
  BULK COLLECT INTO my_SLTable
  FROM   ALL_TABLES
  WHERE  Owner = USER -- 'OW1'
  AND    Table_Name Like 'TN_KO%';

  SELECT t_SLRekord(at.Owner, at.Table_Name)
  BULK COLLECT INTO v_AT_Table -- just an other PL/SQL coollection
  FROM   ALL_TABLES at
         RIGHT OUTER JOIN table(my_SLTable) t
         ON t.Table_Name = at.Table_Name
            AND at.owner = USER -- 'OW3'
         ;

  FOR i IN 1 .. v_at_table.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_at_table(i).owner || '.' || v_at_table(i).table_name);
  END LOOP;
END;
/

Which outputs:

FIDDLE_CICKSCRCWLKQACAKYEBH.TN_KO_1
FIDDLE_CICKSCRCWLKQACAKYEBH.TN_KO_2

fiddle