Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / System Tables Data Dictionary

 

Get information on your nested tables from the user_nested_tables view

SQL> SQL> desc user_nested_tables;  Name          Null?    Type  TABLE_NAME             VARCHAR2(30)    Name of the nested table                                            TABLE_TYPE_OWNER       VARCHAR2(30)    User who owns the nested table type                                 TABLE_TYPE_NAME        VARCHAR2(30)    Name of the nested table type                                       PARENT_TABLE_NAME      VARCHAR2(30)    Name of the parent table that contains the nested table             PARENT_TABLE_COLUMN    VARCHAR2(4000)  Name of the column in the parent table containing the nested table  STORAGE_SPEC           VARCHAR2(30)    Storage specification for the nested table                          RETURN_TYPE            VARCHAR2(20)    Return type of the column                                           ELEMENT_SUBSTITUTABLE  VARCHAR2(25) SQL> CREATE Or Replace TYPE AddressType AS OBJECT (   2    street VARCHAR2(15),   3    city   VARCHAR2(15),   4    state  CHAR(2),   5    zip    VARCHAR2(5)   6  );   7  / SQL> SQL> CREATE Or Replace TYPE nested_table_AddressType AS TABLE OF AddressType;   2  / Type created. SQL> SQL> CREATE TABLE employee (   2    id         INTEGER PRIMARY KEY,   3    first_name VARCHAR2(10),   4    last_name  VARCHAR2(10),   5    addresses  nested_table_AddressType   6  )   7  NESTED TABLE   8    addresses   9  STORE AS  10    nested_addresses; Table created. SQL> SQL> --SELECT * FROM user_nested_tables; SQL> SQL> drop table employee; Table dropped. SQL>