CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Paul
Paul

Reputation: 1167

Get index of element in VARRAY collections

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?

Upvotes: 3

Views: 1361

Answers (1)

MT0
MT0

Reputation: 167962

Use the ROW_NUMBER() analytic function:

SELECT t.id,
       c.COLUMN_VALUE,
       ROW_NUMBER() OVER ( PARTITION BY t.ROWID ORDER BY ROWNUM ) AS idx
FROM   TAB_WITH_ARRAY t
       CROSS JOIN
       TABLE( t.vals ) c

Or, from Oracle 12c, use OUTER APPLY and ROWNUM:

SELECT t.id,
       v.*
FROM   tab_with_array t
       OUTER APPLY (
         SELECT v.COLUMN_VALUE,
                ROWNUM AS idx
         FROM   TABLE( t.vals ) v
       ) v

Which, for your sample data, both outputs:

ID | COLUMN_VALUE | IDX
-: | -----------: | --:
 1 |            1 |   1
 1 |            2 |   2
 2 |            1 |   1
 2 |            2 |   2
 2 |            3 |   3
 2 |            4 |   4
 2 |            5 |   5

db<>fiddle here

Upvotes: 2

Related Questions