reading VARRAY data types

[Guest_imported]

I have a table with columns ->
name varchar2(20),
phone_nos phones

Here type phones is a varry(5) of varchar2(10). Obviously phone columns may contain null, 1, 2, ...,5 phone numbers, I need to get individual phone numbers which is phone(i) i=1..5. Problem here is if I specify phone(3) when there is only 2 phone nos, then I will get subscript out of range error.

How can I find the number of values in a VARRAY type column?

My work around is insert a known value (e.g. 'NULL') at the last and check for it before incrementing the subscript.
I am sure there is definitely better answer.

thanks in advance.

 

[THoey]

I am kinda curious why you would make this much work for yourself. Since people are going to have several numbers (a one to many relationship), why not make a new table:

Personnel Table
ID Primary Key
Name
Address
...

PhoneNumber Table
ID Foreign Key (link to Personnel Table)
PhoneNum

This way, you have one record for each phone number, whether they have 0, 1, 1000 phone numbers and you don't have to jump through hoops using arrays.

Hope that helps... Terry M. Hoey