Get index of element in VARRAY collections

Question

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?

Possible duplicate of [How do I get the index of VARRAY items after converting to a table](https://stackoverflow.com/questions/10559647/how-do-i-get-the-index-of-varray-items-after-converting-to-a-table) — Sudipta Mondal, Jul 10 '17 at 14:10
Thanks, @SudiptaMondal, that's quite close. However, I'd love to see 1) an entirely SQL solution, 2) a solution that works with a column of arrays, not a single array. — Paul, Jul 10 '17 at 15:24

MT0 · Answer 1 · 2021-01-03T01:25:49.057

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

This `ORDER BY rownum` thing is cool. I suppose I could isolate this part of a query and use it to preserve the order of output. The issue is I never found a firm statement in docs that `TABLE()` outputs rows in the same order the elements are stored in the array. Other than that, nice answer! Thanks! — Paul, Jul 10 '17 at 15:36

Get index of element in VARRAY collections

1 Answers1