Cross join to varray without using Table() expression-CodePudding

I'm trying to learn about cross joining to varray collections.

Example 1: The following query works. It propagates rows via cross joining to the elements in the varray column letter_array, using the Table() expression:

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

select 
    cte.id,
    t.column_value
from 
    cte
cross join
    table(letter_array) t

   ID   COLUMN_VALUE
-----   -----------
    1   a    
    1   b    

    2   c    
    2   d    
    2   e    
    2   f

It produces n rows for each ID (n is a varying number of elements in the letter_array).

Example 2: In a related post, we determined that the TABLE() keyword for collections is now optional.

So, in the next example (with slightly different data), I'll use sys.odcivarchar2list('c', 'd', 'e', 'f') hardcoded in the join, without using the TABLE() expression:

with cte as (
select 1 as id from dual union all
select 2 as id from dual)

select 
    *
from 
    cte
cross join
    sys.odcivarchar2list('c', 'd', 'e', 'f')

   ID   COLUMN_VALUE
-----   ------------
    1   c    
    1   d    
    1   e    
    1   f    

    2   c    
    2   d    
    2   e    
    2   f

That works as expected too. It produces 4 rows for each ID (4 is the number of elements in the hardcoded varray expression).

Example 3: However, what I actually want, is to use the data from the first example, except this time, use a varray column in the join, without using the TABLE() expression:

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

select 
    cte.id,
    t.column_value
from 
    cte
cross join
    letter_array t

Error:

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 11 Column: 10

Question:

How can I cross join to the varray without using the Table() expression?

CodePudding user response：

Use CROSS APPLY:

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

Which outputs:

ID COLUMN_VALUE

1 a

1 b

2 c

2 d

2 e

2 f

ID	COLUMN_VALUE
1	a
1	b
2	c
2	d
2	e
2	f

Note: Using a legacy comma join, ANSI CROSS JOIN and CROSS JOIN LATERAL all fail, in Oracle 18c and 21c, without the TABLE keyword and succeed with it. See the fiddle below.

Note 2: TABLE(...) is not a function, it is the syntax for a table collection expression (which wraps a collection expression).

db<>fiddle here