Oracle 18c:
As an experiment:
What techniques are available for generating rows for each ordinate?
For example:
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
Result:
LINE_ID ORDINATE_INDEX ORDINATE_VAL
------- -------------- ------------
A 1 101
A 2 102
A 3 103
A 4 104
B 1 105
B 2 106
B 3 107
B 4 108
B 5 109
B 6 110
C 1 111
C 2 112
C 3 113
C 4 114
C 5 115
C 6 116
C 7 117
C 8 118
Are there any other ways to get ordinates as rows in a query?