I would imagine this is a simple task, but it keeps giving me errors. I have a table for storing a person's id and their phone numbers, let's say:

create or replace type phone_array is varray(2) of number(10);
create table p_phones (p_id number not null primary key,phones phone_array);
insert into p_phones values (1,phone_array(12,34));

I am trying to write a select statement that will give me the first number,

select phones from p_phones;

gives a phone array as expected, but

select phones(1) from p_phones;

gives an error - how can I combine it with a constraint on person id and select a specific value from the varray?

Best Regards, Gee

I would imagine this is a simple task, but it keeps giving me errors. I have a table for storing a person's id and their phone numbers, let's say:

create or replace type phone_array is varray(2) of number(10);
create table p_phones (p_id number not null primary key,phones phone_array);
insert into p_phones values (1,phone_array(12,34));

I am trying to write a select statement that will give me the first number,

select phones from p_phones;

gives a phone array as expected, but

select phones(1) from p_phones;

gives an error - how can I combine it with a constraint on person id and select a specific value from the varray?

Best Regards, Gee

• oracle-database
• varray

2 Answers 2

You can use a LATERAL join to a TABLE collection expression and filter to a specific row:

select p.p_id,
       v.phone_number
from   p_phones p
       CROSS JOIN LATERAL(
         SELECT COLUMN_VALUE AS phone_number
         FROM   TABLE(p.phones)
         OFFSET 0 ROWS
         FETCH NEXT 1 ROW ONLY
       ) v;

Or you can use a similar technique in a correlated sub-query:

select p.p_id,
       ( SELECT COLUMN_VALUE
         FROM   TABLE(phones)
         OFFSET 0 ROWS
         FETCH FIRST ROW ONLY
       ) AS phone_number
from   p_phones p;

Or you can CROSS JOIN to a TABLE collection expression to get all the rows and then use ROW_NUMBER to filter to the correct row for each p_id:

SELECT p_id,
       phone_number
FROM   (
  SELECT p.p_id,
         v.column_value AS phone_number,
         ROW_NUMBER() OVER (PARTITION BY p.p_id ORDER BY ROWNUM) AS rn
  FROM   p_phones p
         CROSS JOIN TABLE(p.phones) v
)
WHERE  rn = 1;

Which all output:

If you need to filter for a specific p_id then add a WHERE clause with the appropriate filter.

fiddle

You can use WITH FUNCTION:

with
  function get(arr phone_array, i integer)
    return number
    as
        out_of_limits exception ;
        pragma exception_init(out_of_limits, -6532);
    begin
      return arr(i);
    exception when out_of_limits then
        return null;
    end;
select get(phones,1) from p_phones;