fifamaniac04 fifamaniac04 - 2 months ago 6
SQL Question

How to Select value in Oracle Varray

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);

empName('Ramos', null, 'Phil'),
AddressType(123, 'A Street', 'San Diego'),
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 (||' '||||' '|| as "Name" FROM Workers w; --this works

--Display their location
--SELECT (w.loc.streetNumber||' '||w.loc.StreetName||', '|| 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
so that I get a list of phone numbers ?

I want my output to look something like:

phone Numbers
1234567890 <cell>
2345678901 <home>


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.