Why does CROSS JOIN LATERAL break up array’s SDO_GEOMETRY object into individual attributes?

Oracle 18c:

I have mapping software that has a limitation where it can only handle a single geometry column per table. If there are multiple geometry columns in a given table, then it will throw an error.

So, I want to find a way to add an additional geometry column to a table, but store it as a datatype that the mapping software doesn’t recognize — so it will ignore that column (TBD).

One idea is to store an SDO_GEOMETRY as an SDO_GEOMETRY_ARRAY datatype, since the software wouldn’t recognize SDO_GEOMETRY_ARRAY. I would always only store a single geometry in the array.

Similar to this:

with data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select geom_array from data

GEOM_ARRAY (SQL Developer)
----------------------------------------------
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])

---

Unsurprisingly, when I select from the array, it returns the entire array, not the SDO_GEOMETRY (even though there’s only a single value in the array).

So, I want to find an easy/succinct way to extract the SDO_GEOMETRY from the array.

I could use a custom function, which works as expected:

with 
function get_geom_from_array(geom_array sdo_geometry_array) return sdo_geometry is
begin
    return geom_array(1);
end;    
data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select
    get_geom_from_array(geom_array)
from   
    data

SDO_GEOM
---------------
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]

Or I could use a CROSS JOIN LATERAL:

select
    v.*
from   
    data d
cross join lateral (
    select sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates) as sdo_geom
    from   table(d.sdo_array)
    ) v

SDO_GEOM
---------------
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]

That CROSS JOIN LATERAL works, but I don’t understand why it splits the SDO_GEOMETRY into it’s attribute components:

select
    v.*
from   
    data d
cross join lateral (
    select *
    from   table(d.sdo_array)
    ) v

SDO_GTYPE SDO_SRID SDO_POINT              SDO_ELEM_INFO SDO_ORDINATES
--------- -------- ---------------------- ------------- -------------
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null

As such, I need to reconstruct the geometry from those attributes, like this: sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates).

Reconstructing the SDO_GEOMETRY is slightly inconvenient. But also, I want to understand why that happens in the CROSS JOIN LATERAL, but not in the custom function.

---

Question:

Why does the CROSS JOIN LATERAL break up the array’s SDO_GEOMETRY object into individual attributes? (but the function doesn’t)

Answer

Why does the CROSS JOIN LATERAL break up the array’s SDO_GEOMETRY object into individual attributes? (but the function doesn’t)

CROSS JOIN LATERAL is treating the table collection expression as if it is an object-derived table (which it effectively is) and uses each object of the collection to form a row of a table and then the columns are the attributes of the object.

The function is returning a single object into one column of one row. They are very different operations.

---

If you want to get the objects that comprise each row from the table collection expression then use the VALUE function:

SELECT v.obj
FROM   data d
       CROSS JOIN LATERAL (
         SELECT VALUE(t) AS obj
         FROM   TABLE(d.sdo_array) t
       ) v

db<>fiddle here