Query a table with user-defined column type : Object Column « Object Oriented « Oracle PL/SQL Tutorial

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 * 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.

32.7.Object Column
	32.7.1.	Using Object Types to Define Column Objects and Object Tables
	32.7.2.	You can use an object type to define an entire table, and the table is known as an object table.
	32.7.3.	The Object Type Column Objects
	32.7.4.	Loading the 'row object' Table
	32.7.5.	SELECT with a WHERE Clause
	32.7.6.	UPDATE Data in a Table of Row Objects
	32.7.7.	Using UPDATE with TYPEed Columns
	32.7.8.	Query a table with user-defined column type
	32.7.9.	Use * to reference all columns from a table
	32.7.10.	Use 'table of custom type' as table column type
	32.7.11.	Nested varray and table collection column
	32.7.12.	Create type and use it as table column
	32.7.13.	Nested type Column
	32.7.14.	Create a new type and add it to a table