Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH elements (line_id, elem_no, starting_offset, next_offset, ordinates) AS (
  SELECT '001' as line_id,
         elem_no,
         starting_offset,
         next_offset,
         ordinates
  FROM   JSON_TABLE(
           JSON_OBJECT(
             SDO_GEOMETRY(
               'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))'
             )
           ),
           '$?(@.SDO_GTYPE==2006)'
           COLUMNS (
             ordinates       VARCHAR2(4000) FORMAT JSON PATH '$.SDO_ORDINATES',
             NESTED PATH '$.SDO_ELEM_INFO[*]' COLUMNS (
               item_no FOR ORDINALITY,
               item    NUMBER PATH '$'
             )
           )
         )
  MATCH_RECOGNIZE(
    ORDER BY item_no
    MEASURES
      MATCH_NUMBER() AS elem_no,
      FIRST(starting_offset.item) AS starting_offset,
      FIRST(etype.item)           AS etype,
      FIRST(interpretation.item)  AS interpretation,
      NEXT(item)                  AS next_offset,
      FIRST(ordinates)            AS ordinates
    PATTERN (starting_offset etype interpretation)
    DEFINE starting_offset AS 1 = 1
  ) m
  WHERE (etype, interpretation) IN ((2, 1))
)
SELECT m.*
FROM   elements e
       CROSS JOIN LATERAL(
         SELECT *
         FROM   JSON_TABLE(
                  e.ordinates,
                  '$[*]'
                  COLUMNS (
                    coord_no FOR ORDINALITY,
                    coord    NUMBER PATH '$'
                  )
                )
         WHERE  coord_no >= e.starting_offset
         AND    (e.next_offset IS NULL OR coord_no < e.next_offset)
       )
MATCH_RECOGNIZE (
  PARTITION BY line_id, elem_no
  ORDER BY coord_no
  MEASURES
    MATCH_NUMBER() AS coord_id,
    FIRST(x.coord) AS X,
    FIRST(y.coord) AS Y
  PATTERN (X Y)
  DEFINE X AS 1 = 1
) m

LINE_ID	ELEM_NO	COORD_ID	X	Y
001	1	1	0	5
001	1	2	10	10
001	1	3	30	0
001	2	1	50	10
001	2	2	60	10

LINE_ID	ELEM_NO	COORD_ID	X	Y
001	1	1	0	5
001	1	2	10	10
001	1	3	30	0
001	2	1	50	10
001	2	2	60	10