Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Object Oriented

 

Query a table with user-defined column type

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 * from people; NAME                           DOB ------------------------------ --------- HOME_ADDRESS(CITY, STREET, STATE, ZIP) ---------------------------------------------------------------------------------------------------- WORK_ADDRESS(CITY, STREET, STATE, ZIP) ---------------------------------------------------------------------------------------------------- Tom                            15-MAR-65 ADDRESS_TYPE('R', '1 Street', 'Va', 45678) ADDRESS_TYPE('R', '1 Way', 'Ca', 23456) Tom                            15-MAR-65 ADDRESS_TYPE('R', '1 Street', 'Va', 45678) ADDRESS_TYPE('R', '1 Way', 'Ca', 23456) NAME                           DOB ------------------------------ --------- HOME_ADDRESS(CITY, STREET, STATE, ZIP) ---------------------------------------------------------------------------------------------------- WORK_ADDRESS(CITY, STREET, STATE, ZIP) ---------------------------------------------------------------------------------------------------- SQL> SQL> drop table people; Table dropped. SQL> drop type person_type; Type dropped. SQL> drop type address_type; Type dropped.