Reference nested data type in select statement

    


SQL> create type addressType as object
  2  (Street   VARCHAR2(50),
  3  City      VARCHAR2(25),
  4  State     CHAR(2),
  5  Zip       NUMBER);
  6  /
SQL>
SQL> create type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create or replace type personType as object
  2  (Name     VARCHAR2(25),
  3   Address  addressType);
  4  /
SQL>
SQL>
SQL> create table myemp
  2  (cid    NUMBER,
  3   Person         personType);
SQL>
SQL>
SQL> insert into myemp values
  2  (1,personType('SomeName',
  3     addressType('StreetValue','CityValue','ST',11111)));
SQL>
SQL>
SQL> select C.Person.Name,
  2         C.Person.Address.City
  3    from myemp C
  4   where C.Person.Address.City like 'C%';
                                                              

PERSON.NAME                PERSON.ADDRESS.CITY
-------------------------  -------------------------
SomeName                   CityValue
                   
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>
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.	Check object table column type
7.	Use varray in a table
8.	Nested type Column
9.	This script demonstrates column objects.
10.	Reference type column

Reference nested data type in select statement : Object Column « Object Oriented Database « Oracle PL / SQL

Related examples in the same category