Get Information on Nested Table

DESCRIBE comman shows the information on a nested table:


CREATE TYPE t_address AS OBJECT (
    street VARCHAR2(15),
    city VARCHAR2(15),
    state CHAR(2),
    zip VARCHAR2(5)
);
/

CREATE TYPE t_nested_table AS TABLE OF t_address;
/


SQL> DESCRIBE t_nested_table
 t_nested_table TABLE OF T_ADDRESS
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------

 STREET                                             VARCHAR2(15)
 CITY                                               VARCHAR2(15)
 STATE                                              CHAR(2)
 ZIP                                                VARCHAR2(5)

SQL>


CREATE TABLE emp (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR2(10),
    last_name VARCHAR2(10),
    addresses t_nested_table_address
)
NESTED TABLE addresses
STORE AS nested_addresses;

SQL> DESCRIBE emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 ID                                        NOT NULL NUMBER(38)
 FIRST_NAME                                         VARCHAR2(10)
 LAST_NAME                                          VARCHAR2(10)
 ADDRESSES                                          T_NESTED_TABLE

SQL>

If you set the depth to 2 


SQL> SET DESCRIBE DEPTH 2
SQL> DESCRIBE emp
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------------

 ID                                        NOT NULL NUMBER(38)
 FIRST_NAME                                         VARCHAR2(10)
 LAST_NAME                                          VARCHAR2(10)
 ADDRESSES                                          T_NESTED_TABLE
   STREET                                           VARCHAR2(15)
   CITY                                             VARCHAR2(15)
   STATE                                            CHAR(2)
   ZIP                                              VARCHAR2(5)

SQL>

The user_nested_tables view has information on your nested tables. The all_nested_tables view has information on all the nested tables you have access to. 


SELECT table_name, 
       table_type_name, 
       parent_table_name, 
       parent_table_column
FROM user_nested_tables
WHERE table_name = 'YOUR_NESTED_TABLE_NAME';
Home »
Oracle »
PL/SQL » 

Nested Table:
  1. Creating a Nested Table Type
  2. Using a Nested Table Type to Define a Column
  3. Get Information on Nested Table
  4. Populating a Nested Table with Elements
  5. Retrieving Elements from a Nested Table
  6. Using TABLE() with a Nested Table
Related: