How to Select value in Oracle Varray

Question

Working in Oracle 11.2

I have created a type for phone numbers to be associated with an employee.... I'm trying to learn syntax and DO NOT want to be criticized on good or bad database deign...

here is my *.sql file

CREATE TYPE AddressType AS OBJECT(streetNumber NUMBER(5), StreetName VARCHAR2(30), city VARCHAR2(20));
/
CREATE TYPE empName AS OBJECT(firstname VARCHAR2(10), middle VARCHAR2(10), lastname VARCHAR2(10));
/
CREATE TYPE PhoneNumbers AS OBJECT(phNumb NUMBER(10), numType VARCHAR2(10));
/
CREATE TYPE ContactNumbers AS VARRAY(5) OF PhoneNumbers;
/

CREATE TABLE Workers(eid NUMBER(5), name empName, loc AddressType,    contactNums ContactNumbers);

INSERT INTO Workers VALUES( 1,
                        empName('Ramos', null, 'Phil'), 
                        AddressType(123, 'A Street', 'San Diego'), 
                        ContactNumbers(
                                    PhoneNumbers(1234567890, 'cell'),
                                    PhoneNumbers(2345678901, 'home')
                                    )
                      );


--Display all members of workers
SELECT * FROM Workers; -- i get something here I'm ok with 

--Display all the names
--SELECT (w.name.firstname||' '||w.name.middle||' '||w.name.lastname) as     "Name" FROM Workers w; --this works

--Display their location
--SELECT (w.loc.streetNumber||' '||w.loc.StreetName||', '||w.loc.city) as "Location" FROM Workers w; -- this works

--Display their phone numbers
SELECT (w.contactNums(1).phNumb||' <'||w.ContactNumbers(1).numType||'>') as "Phone Numbers" FROM Workers w; -- this line I can't figure out

Q: How do I write the last SELECT so that I get a list of phone numbers ?

I want my output to look something like:

phone Numbers
-------------
1234567890 <cell>
2345678901 <home>

Answer 1

You will have the use the TABLE operator in the case of a VARRAY:

SELECT e.phNumb||' <'||e.numType||'>' as "Phone Numbers"
 FROM Workers w, TABLE(w.contactNums) e;

Phone Numbers                                       
-----------------------------------------------------
1234567890 <cell>                                    
2345678901 <home>    

You can find out more about it in the Database Oject-Relational Developer's Guide.