Where is all_nested_table_cols in 9i?

Question

Oracle 9i has nested tables, however it doesn't have the all_nested_table_cols sysview (like 10g and 11g) which lets me see what the columns are for these nested tables. How can I find this information on a 9i database?

Answer 1

I don't have a 9i instance to test with, but maybe this can get you started:

SELECT nt.owner, nt.table_name, nt.parent_table_name, nt.parent_table_column, ct.owner, ct.type_name, ta.*
  FROM all_nested_tables nt, all_coll_types ct, all_type_attrs ta
 WHERE ct.type_name = nt.table_type_name
   AND ta.type_name = ct.elem_type_name

The attr_name column should be something like the column_name column in all_nested_table_cols. I know it's not the real thing... but it's a start.

Making this CW in case anyone wants to improve it.