SQL & PL/SQL

I need to be able to dynamically create a UNION ALL SQL statement for a supertype/subtype implementation that currently has 45 subtypes with a total of 165 columns.

These supertype/subtype implantations change every time I get a new set of data. The only thing that remains constant is the supertype primary key column name.

This is what I have come up with to find the tables and columns involved:

select
    T.OWNER,
    T.TABLE_NAME,
    C.COLUMN_NAME,
    'NULL as ' || C.COLUMN_NAME as SCRIPT
from
    ALL_TABLES T,
    ALL_TAB_COLUMNS C
where
    T.OWNER = C.OWNER
    and T.TABLE_NAME = C.TABLE_NAME
    and T.TABLE_NAME <> 'ITEM'
    and C.COLUMN_NAME <> 'SUPER_UID'
    and T.TABLE_NAME in
    (
    select
        T1.TABLE_NAME
    from
        ALL_TABLES T1,
        ALL_TAB_COLUMNS C1
    where
        T1.OWNER = C1.OWNER
        and T1.TABLE_NAME = C1.TABLE_NAME
        and C1.COLUMN_NAME = 'SUPER_UID'
    )

The "SCRIPT" column is my attempt at the SQL statement.

This is what I need the output to look like:

select
    SUPER.SUPER_UID as SUPER_UID,
    SUPER.ITEM_NAME as ITEM_NAME,
    SUPER.ITEM_DESC as ITEM_DESC,
    SUPER.ITEM_TYPE as ITEM_TYPE,
    SUB_A.ITEM_SIZE as ITEM_SIZE,
    SUB_A.TIEM_COLOR as TIEM_COLOR,
    NULL as ITEM_HIGHT, 
    NULL as ITEM_DEPTH, 
    NULL as ITEM_LENGTH, 
    NULL as ITEM_WEIGHT
from 
    ITEM SUPER,
    ITEM_A SUB_A
where
    SUPER.SUPER_UID = SUB_A.SUPER_UID 
    and SUPER.ITEM_TYPE = 'TYPE_A'
union all
select
    SUPER.SUPER_UID as SUPER_UID,
    SUPER.ITEM_NAME as ITEM_NAME,
    SUPER.ITEM_DESC as ITEM_DESC,
    SUPER.ITEM_TYPE as ITEM_TYPE,
    NULL as ITEM_SIZE,
    NULL as TIEM_COLOR,
    SUB_B.ITEM_HIGHT as ITEM_HIGHT, 
    SUB_B.ITEM_DEPTH as ITEM_DEPTH, 
    SUB_B.ITEM_LENGTH as ITEM_LENGTH, 
    NULL as ITEM_WEIGHT
from 
    ITEM SUPER,
    ITEM_B SUB_B
where
    SUPER.SUPER_UID = SUB_B.SUPER_UID 
    and SUPER.ITEM_TYPE = 'TYPE_B'
union all
select
    SUPER.SUPER_UID as SUPER_UID,
    SUPER.ITEM_NAME as ITEM_NAME,
    SUPER.ITEM_DESC as ITEM_DESC,
    SUPER.ITEM_TYPE as ITEM_TYPE,
    NULL as ITEM_SIZE,
    NULL as TIEM_COLOR,
    NULL as ITEM_HIGHT, 
    NULL as ITEM_DEPTH, 
    NULL as ITEM_LENGTH, 
    SUB_C.ITEM_WEIGHT as ITEM_WEIGHT
from 
    ITEM SUPER,
    ITEM_C SUB_C
where
    SUPER.SUPER_UID = SUB_C.SUPER_UID 
    and SUPER.ITEM_TYPE = 'TYPE_C'