你可以使用一个函数：

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

字符串
或CROSS JOIN LATERAL（或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

型
这两个输出：
| START_POINT_Y |
| ------------ |
| 2 |
| 6 |
| 12 |

应该可以，但没有：

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

型
和/或

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

型
两者都应该工作，但输出：
| STARTPOINT_Y |
| ------------ |
| 2 |
| 2 |
| 2 |

• db<>小提琴here *

你确实得到了问题的帮助，最好是摆脱这里的rownum-filter，但我猜你仍然错过了答案，为什么rownum = 1完成了这项工作，而rownum = 2没有。
请注意：在Oracle中，你永远不能要求rownum = 2或类似rownum > 2的值。很久以前我读过一个关于这个问题的解释，我不能再给予你解释了，但据我所知，这与“rownum”的求值时间有关。如果你不想让rownum = 1并过滤掉它，那么你永远不会有rownum = 2，这是我脑海中的缩写形式。
如果你将来真的需要处理类似的东西，你可以使用row_number（）函数。

下面是@MTO的第二个查询的通用版本。它将 * 所有 * 坐标作为行，而不仅仅是startpoint_y：
或横向交叉接合（或横向应用）：

• （为了清晰起见，我修改了原始问题的样本数据。）*

with cte as (
  select 'A' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(101,102, 103,104                  )) shape from dual union all
  select 'B' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(105,106, 107,108, 109,110         )) shape from dual union all
  select 'C' as line_id, sdo_geometry(2002, 26917, null, sdo_elem_info_array(1, 2, 1), sdo_ordinate_array(111,112, 113,114, 115,116, 117,118)) shape from dual
)
select 
    cte.line_id,
    v.ordinate_index,
    v.ordinate_val
from   
    cte
cross join lateral (         --CROSS APPLY works too
                    select 
                        rownum as ordinate_index,
                        column_value as ordinate_val 
                    from   
                        table((shape).sdo_ordinates)
                    ) v
--where
    --ordinate_index = 1 --startpoint_x    
    --ordinate_index = 2 --startpoint_y
    --ordinate_index = (sdo_util.getnumvertices(cte.shape)*2) -1 --endpoint x; assumes each vertex only has 2 dimensions (is 2d and not LRS)
    --ordinate_index = (sdo_util.getnumvertices(cte.shape)*2)    --endpoint y; assumes each vertex only has 2 dimensions (is 2d and not LRS)

个字符