Join sys.col$ and user_objects
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 /
SQL>
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> desc people
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
DOB DATE
HOME_ADDRESS ADDRESS_TYPE
WORK_ADDRESS ADDRESS_TYPE
SQL>
SQL>
SQL> select * from people;
no rows selected
SQL>
SQL> select name, p.home_address.city from people p;
no rows selected
SQL>
SQL> select name, segcollength from sys.col$
2 where obj# = ( select object_id from user_objects where object_name = 'PEOPLE' )
3 /
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
SYS_NC00012$ 30
SYS_NC00013$ 2
SYS_NC00014$ 22
14 rows selected.
SQL>
SQL> drop table people;
Table dropped.
SQL>
SQL>
Related examples in the same category