Working with SDO_GEOMETRY ordinates as a table in a column subquery

Oracle 18c:

I'm experimenting with different techniques for working with SDO_GEOMETRY vertices in queries.

For example, working with ordinates as a table in a column subquery:

with cte as (
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual)

select 
    (select 
        column_value
    from
        table((shape).sdo_ordinates) 
    where 
        rownum = 1       --Side note: "FETCH FIRST ROW ONLY" doesn't work the way I expected. It selects 1 for each startpoint X, which is incorrect. I'm not sure why it works that way.
    ) startpoint_x
from 
    cte

STARTPOINT_X
------------
           1
           5
          11

That query works as expected. It gets the startpoint X for each geometry.

Similarly, I had wondered if I could use the same technique to get the startpoint Y for each geometry. I would do this by changing rownum = 1 to rownum = 2 to get the second ordinate from the list of ordinates:

with cte as (
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual)

select 
    (select 
        column_value
    from
        table((shape).sdo_ordinates) 
    where 
        rownum = 2
    ) startpoint_y
from 
    cte

STARTPOINT_Y
------------
      (null)
      (null)
      (null)

But that didn't work the way I thought it would. It returned nulls, whereas I wanted it to return:

STARTPOINT_Y
------------
           2
           6
          12

Question:

Why did that query work for startpoint X rownum = 1, but not startpoint Y rownum = 2?

I'm aware that there are other ways to interact with vertices, such as cross join table(sdo_util.getvertices(shape)). That works, but I want to learn about how ordinates behave as a table in a column subquery.

You can use a function:

with FUNCTION get_ordinate(
  shape SDO_GEOMETRY,
  idx   PLS_INTEGER
) RETURN NUMBER
IS
BEGIN
  RETURN shape.sdo_ordinates(idx);
END;
cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select get_ordinate(c.shape, 2) AS start_point_y
from   cte c

or, CROSS JOIN LATERAL (or CROSS APPLY):

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select sp.start_point AS start_point_y
from   cte c
       CROSS JOIN LATERAL (
         SELECT column_value AS start_point, ROWNUM AS start_point_index
         FROM   table(c.shape.sdo_ordinates)
       ) sp
WHERE  sp.start_point_index = 2

Which both output:

START_POINT_Y
2
6
12

This should work but does not:

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select ( SELECT column_value
         FROM   (
           select column_value, ROWNUM AS rn
           from   table(c.shape.sdo_ordinates) 
           WHERE  ROWNUM <= 2
         )
         WHERE rn = 2
       ) AS startpoint_y
from   cte c

and

with cte as (
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 1, 2,  3, 4              )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array( 5, 6,  7, 8,  9,10       )) shape from dual union all
  select sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(11,12, 13,14, 15,16, 17,18)) shape from dual
)
select ( select column_value
         from   table(c.shape.sdo_ordinates) 
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) AS startpoint_y
from   cte c

Both should work but output:

STARTPOINT_Y
2
2
2

db<>fiddle here