Cross-joining with a table object propagates rows (without needing Table() function)-CodePudding

Oracle Spatial has a function called SDO_UTIL.GETVERTICES:

This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of MDSYS.VERTEX_TYPE.
CREATE TYPE vertex_set_type as TABLE OF vertex_type;

The GetVertices() function is typically used in conjunction with the Table() function. The geometry table is cross-joined with table(getvertices(shape)) to propagate vertex rows for each geometry:

with cte as (
select sdo_geometry('linestring (10 20, 30 40)')                       shape from dual union all
select sdo_geometry('linestring (50 60, 70 80, 90 100)')               shape from dual union all
select sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') shape from dual)
  
select 
    v.*
from 
    cte, table(sdo_util.getvertices(shape)) v

    X     Y     Z     W    V5    V6    V7    V8    V9   V10   V11    ID
----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- -----
   10    20                                                           1
   30    40                                                           2
   50    60                                                           1
   70    80                                                           2
   90   100                                                           3
  110   120                                                           1
  130   140                                                           2
  150   160                                                           3
  170   180                                                           4

And the examples in the docs suggest using Table() as well.

But from a quick test, using the table() function doesn't seem to be necessary. If I remove the Table() function from the query, it produces the same result.

Question:

How is it possible that cross-joining with a table object propagates rows — without needing the Table() function?

The docs suggest that we should use the Table() function, so I wonder if that was correct in older versions of Oracle, but maybe something changed in newer versions?

CodePudding user response：

How is it possible that cross-joining with a table object propagates rows — without needing the Table() function?

In either Oracle 12 or Oracle 18, the TABLE keyword for table collection expressions was made an optional part of the syntax so:

SELECT *
FROM   TABLE(SYS.ODCIVARCHAR2LIST('a', 'b', 'c'));

Will work on all oracle versions (that support collections).

And:

SELECT *
FROM   SYS.ODCIVARCHAR2LIST('a', 'b', 'c');

Will work from Oracle 18 (and maybe Oracle 12) and do exactly the same thing (but would raise syntax errors in earlier versions).

I'm trying to find the documentation for the new feature but so far haven't found it.