Get index of element in VARRAY collections

Viewed 1014

This may seem straightforward, but I couldn't find the answer in docs.

As with nested tables, we can use TABLE() with COLUMN_VALUE pseudocolumn for varrays.

create or replace type NUMBER_ARRAY as VARRAY(10) of NUMBER;

create table TAB_WITH_ARRAY(
  ID    NUMBER,
  VALS  NUMBER_ARRAY)    

insert into TAB_WITH_ARRAY
select 1, NUMBER_ARRAY(1,2)
from dual
union all
select 2, NUMBER_ARRAY(1,2,3,4,5)
from dual

select t.id, c.column_value
from TAB_WITH_ARRAY   t,
     table(t.vals)   c

However, unlike nested tables, VARRAY is an ordered collection type, and I want to preserve that order. Is there a way to get not only value but also index of each element in SQL?


Yes, in my tests the order of output was right and I could just use ROW_NUMBER with PARTITION BY primary key of my main table to generate indexes, but experience taught me not rely on ordering unless it was manually specified.

So is there a built-in way to access indexes of elements in array?

1 Answers
Related