Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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 ( id, vals )
select 1, NUMBER_ARRAY(1,2) from dual union all
select 2, NUMBER_ARRAY(1,2,3,4,5) from dual
2 rows affected

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


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


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


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