Best Of

The TABLE collection expression has pseudocolumn COLUMN_VALUE. It would be useful to add a new pseudocolumn named for example ROW_ORDINALITY (inspired by the FOR ORDINALITY clause in xml/jsontable and external tables.)

Example:

create type skills_t as table of varchar2(10);
/

create table emp2
nested table skills store as emp2_skills
as
select empno, ename, skills_t('SQL', 'C#') as skills
from scott.emp
where job = 'ANALYST';

Idea is to enable this syntax:

select
e.empno, e.ename, s.ROW_ORDINALITY as ordinal, s.COLUMN_VALUE as skill
from emp2 e, TABLE(e.skills) s
order by e.empno, ordinal;

Or inspired by the comment of Lukas Eder:

select
e.empno, e.ename, s.ORDINALITY as ordinal, s.COLUMN_VALUE as skill
from emp2 e, TABLE(e.skills) WITH ORDINALITY s
order by e.empno, ordinal;

To get this output:

     EMPNO ENAME         ORDINAL SKILL     
---------- ---------- ---------- ----------
      7788 SCOTT               1 SQL       
      7788 SCOTT               2 C#        
      7902 FORD                1 SQL       
      7902 FORD                2 C#        

Current workaround is something like this:

select
e.empno, e.ename,
row_number() over (partition by e.empno order by ROWNUM) as ordinal,
s.COLUMN_VALUE as skill
from emp2 e, TABLE(e.skills) s
order by e.empno, ordinal;

Which actually isn't guaranteed by Oracle to give the desired result.