Pooja

• Sep 3rd, 2012

 

There are two general ways to query a table that contains a collection type as a column or attribute.

Nest the collections in the result rows that contain them.

Distribute or unnest collections so that each collection element appears on a row by itself.

Code
SELECT d.dept_emps 


  FROM department_persons d;

DEPT_EMPS(IDNO, NAME, PHONE)
-------------------------------------------------------------
PEOPLE_TYP(PERSON_TYP(1, John Smith, 1-650-555-0135),
PERSON_TYP(2, Diane Smith, 1-650-555-0135))

Code
SELECT e.* 


  FROM department_persons d, TABLE(d.dept_emps) e;

IDNO NAME PHONE
---------- ------------------------------ ---------------
1 John Smith 1-650-555-0135
2 Diane Smith 1-650-555-0135