Oracle PL/SQL Tutorial/System Tables Data Dictionary/col

Query name and segment column length from sys.col table

SQL>
SQL>
SQL> create or replace type address_type as object
  2    ( city    varchar2(30),
  3      street  varchar2(30),
  4      state   varchar2(2),
  5      zip     number
  6    )
  7  /
Type created.
SQL> create or replace type person_type as object
  2    ( name             varchar2(30),
  3      dob              date,
  4      home_address     address_type,
  5      work_address     address_type
  6    )
  7  /
Type created.
SQL> create table people of person_type
  2  /
Table created.
SQL>
SQL> select name, segcollength
  2      from sys.col$
  3     where obj# = ( select object_id
  4                      from user_objects
  5                     where object_name = "PEOPLE" )
  6  /
NAME                           SEGCOLLENGTH
------------------------------ ------------
SYS_NC_OID$                              16
SYS_NC_ROWINFO$                           1
NAME                                     30
DOB                                       7
HOME_ADDRESS                              1
SYS_NC00006$                             30
SYS_NC00007$                             30
SYS_NC00008$                              2
SYS_NC00009$                             22
WORK_ADDRESS                              1
SYS_NC00011$                             30
NAME                           SEGCOLLENGTH
------------------------------ ------------
SYS_NC00012$                             30
SYS_NC00013$                              2
SYS_NC00014$                             22
14 rows selected.
SQL>
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.
SQL>

Reference column with user-defined type in select statement

SQL>
SQL>
SQL> create or replace type address_type as object
  2    ( city    varchar2(30),
  3      street  varchar2(30),
  4      state   varchar2(2),
  5      zip     number
  6    )
  7  /
Type created.
SQL> create or replace type person_type as object
  2    ( name             varchar2(30),
  3      dob              date,
  4      home_address     address_type,
  5      work_address     address_type
  6    )
  7  /
Type created.
SQL> create table people of person_type
  2  /
Table created.
SQL> insert into people values ( "Tom", "15-mar-1965",
  2    address_type( "R", "1 Street", "Va", "45678" ),
  3    address_type( "R", "1 Way", "Ca", "23456" ) );
1 row created.
SQL> /
1 row created.
SQL>
SQL> select name, p.home_address.city from people p;
NAME                           HOME_ADDRESS.CITY
------------------------------ ------------------------------
Tom                            R
Tom                            R
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL> drop type person_type;
Type dropped.
SQL> drop type address_type;
Type dropped.