How to Select value in Oracle Varray
<p>Working in Oracle 11.2 </p>

<p>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...</p>

<p>here is my *.sql file</p>

<pre><code>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||' &lt;'||w.ContactNumbers(1).numType||'&gt;') as "Phone Numbers" FROM Workers w; -- this line I can't figure out
</code></pre>

<p>Q: How do I write the last <code>SELECT</code> so that I get a list of phone numbers ?</p>

<p>I want my output to look something like:</p>

<pre><code>phone Numbers
-------------
1234567890 &lt;cell&gt;
2345678901 &lt;home&gt;
</code></pre>