Oracle 21c

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

select text
from   all_source
where  type  = 'TYPE'
and    owner = 'MDSYS'
and    name  = 'ST_CURVE'
ORDER BY line


TEXT


TYPE ST_CURVE


                                        UNDER ST_GEOMETRY (


  OVERRIDING MEMBER FUNCTION ST_Dimension RETURN Integer DETERMINISTIC,


  MEMBER FUNCTION ST_Points RETURN ST_Point_Array DETERMINISTIC,


  MEMBER FUNCTION ST_NumPoints RETURN INTEGER DETERMINISTIC,


  MEMBER FUNCTION ST_PointN(aposition INTEGER) RETURN ST_Point DETERMINISTIC,


  MEMBER FUNCTION ST_IsClosed RETURN Integer DETERMINISTIC,


  MEMBER FUNCTION ST_MidPointRep RETURN ST_Point_Array DETERMINISTIC,


  MEMBER FUNCTION ST_StartPoint RETURN ST_Point DETERMINISTIC,


  MEMBER FUNCTION ST_EndPoint RETURN ST_Point DETERMINISTIC,


  OVERRIDING MEMBER FUNCTION ST_IsSimple RETURN Integer DETERMINISTIC,


  MEMBER FUNCTION ST_IsRing RETURN Integer DETERMINISTIC,


  MEMBER FUNCTION ST_Length RETURN NUMBER DETERMINISTIC) NOT FINAL


with cte (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select cte.asset_id,
       v.id as vertex_id,
       v.x,
       v.y
from   cte,
       sdo_util.getvertices(shape) v


ASSET_ID
VERTEX_ID
X
Y


100
1
10
20

100
2
30
40

200
1
50
60

200
2
70
80

200
3
90
100

300
1
110
120

300
2
130
140

300
3
150
160

300
4
170
180


with cte (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
       p.point
from   cte c
       CROSS JOIN LATERAL (
         SELECT ST_LINESTRING(c.shape).ST_PointN(LEVEL) AS point
         FROM   DUAL
         CONNECT BY LEVEL <= ST_LINESTRING(c.shape).ST_NumPoints()
       ) p;
with cte (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select c.asset_id,
       JSON_OBJECT(p.point)
from   cte c
       CROSS JOIN LATERAL (
         SELECT ST_LINESTRING(c.shape).ST_PointN(LEVEL) AS point
         FROM   DUAL
         CONNECT BY LEVEL <= ST_LINESTRING(c.shape).ST_NumPoints()
       ) p;


ASSET_ID
JSON_OBJECT(P.POINT)


100
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":10,"Y":20,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

100
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":30,"Y":40,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

200
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":50,"Y":60,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

200
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":70,"Y":80,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

200
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":90,"Y":100,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

300
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":110,"Y":120,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

300
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":130,"Y":140,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

300
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":150,"Y":160,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

300
{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":170,"Y":180,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

ASSET_ID	VERTEX_ID	X	Y
100	1	10	20
100	2	30	40
200	1	50	60
200	2	70	80
200	3	90	100
300	1	110	120
300	2	130	140
300	3	150	160
300	4	170	180

ASSET_ID	JSON_OBJECT(P.POINT)
100	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":10,"Y":20,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
100	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":30,"Y":40,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
200	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":50,"Y":60,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
200	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":70,"Y":80,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
200	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":90,"Y":100,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
300	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":110,"Y":120,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
300	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":130,"Y":140,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
300	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":150,"Y":160,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}
300	{"GEOM":{"SDO_GTYPE":2001,"SDO_SRID":null,"SDO_POINT":{"X":170,"Y":180,"Z":null},"SDO_ELEM_INFO":[],"SDO_ORDINATES":[]}}

ASSET_ID	VERTEX_ID	X	Y
100	1	10	20
100	2	30	40
200	1	50	60
200	2	70	80
200	3	90	100
300	1	110	120
300	2	130	140
300	3	150	160
300	4	170	180

ASSET_ID	VERTEX_ID	X	Y
100	1	10	20
100	2	30	40
200	1	50	60
200	2	70	80
200	3	90	100
300	1	110	120
300	2	130	140
300	3	150	160
300	4	170	180