Check object table column type : Object Column « Object Oriented Database « Oracle PL / SQL






Check object table column type

     
SQL>
SQL> create or replace type address_type
  2    as object
  3    ( city    varchar2(30),
  4      street  varchar2(30),
  5      state   varchar2(2),
  6      zip     number
  7    )
  8  /

Type created.

SQL> create or replace type person_type
  2    as object
  3    ( name             varchar2(30),
  4      dob              date,
  5      home_address     address_type,
  6      work_address     address_type
  7    )
  8  /

Type created.

SQL> create table people1 of person_type
  2  /

Table created.

SQL> desc people1
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 NAME                                                                                         VARCHAR2(30)
 DOB                                                                                          DATE
 HOME_ADDRESS                                                                                 ADDRESS_TYPE
 WORK_ADDRESS                                                                                 ADDRESS_TYPE

SQL> select name, segcollength
  2      from sys.col$
  3      where obj# = ( select object_id
  4                      from user_objects
  5                      where object_name = 'PEOPLE1' )
  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
SYS_NC00012$                   30
SYS_NC00013$                    2
SYS_NC00014$                   22

14 rows selected.

SQL>
SQL> drop table people1;

Table dropped.

SQL> drop type person_type;

Type dropped.

SQL> drop type address_type;

Type dropped.

SQL> --

   
    
    
    
  








Related examples in the same category

1.The Object Type Column Objects
2.Format column in the object
3.Alter a table with user-defined object to upgrade including data
4.use user-defined type as the column type
5.Query column with user-defined type
6.Use varray in a table
7.Nested type Column
8.This script demonstrates column objects.
9.Reference nested data type in select statement
10.Reference type column