Join sys.col$ and user_objects : sys.col « System Tables Views « Oracle PL / SQL






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

1.Query sys.col$ table with sub query
2.Check column type with user-defined type in sys.col$
3.Join on and where clause