Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte
       CROSS APPLY letter_array t


ID
COLUMN_VALUE


1
a

1
b

2
c

2
d

2
e

2
f


with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c,
       letter_array t
ORA-00942: table or view does not exist

with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c,
       TABLE(letter_array) t


ID
COLUMN_VALUE


1
a

1
b

2
c

2
d

2
e

2
f


with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c
       CROSS JOIN letter_array t
ORA-00942: table or view does not exist

with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c
       CROSS JOIN TABLE(letter_array) t


ID
COLUMN_VALUE


1
a

1
b

2
c

2
d

2
e

2
f


with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c
       CROSS JOIN LATERAL(
         SELECT COLUMN_VALUE
         FROM   c.letter_array
       ) t
ORA-00942: table or view does not exist

with cte (id, letter_array) as (
  select 1, sys.odcivarchar2list('a', 'b')           from dual union all
  select 2, sys.odcivarchar2list('c', 'd', 'e', 'f') from dual
)
select id,
       t.column_value
from   cte c
       CROSS JOIN LATERAL(
         SELECT COLUMN_VALUE
         FROM   TABLE(c.letter_array)
       ) t


ID
COLUMN_VALUE


1
a

1
b

2
c

2
d

2
e

2
f