Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE X
(
  OBJECTID           INTEGER           NOT NULL,
  SHAPE              ST_GEOMETRY
);
INSERT INTO x (objectid, shape)
VALUES (
  1,
  ST_LINESTRING(
    ST_POINT_ARRAY(
      ST_POINT(1, 2, 4326),
      ST_POINT(3, 4, 4326),
      ST_POINT(5, 6, 4326)
    )
  )
);
1 rows affected

SELECT objectid,
       TREAT(x.shape AS ST_LINESTRING).ST_NUMPOINTS() AS numpoints,
       p.*
FROM   x
       CROSS JOIN LATERAL (
         SELECT p.st_x() AS x,
                p.st_y() AS y
         FROM   TABLE(TREAT(x.shape AS ST_LINESTRING).ST_POINTS()) p
       ) p


OBJECTID
NUMPOINTS
X
Y


1
3
1
2

1
3
3
4

1
3
5
6