By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH data (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 asset_id,
point_array
FROM data d
CROSS JOIN LATERAL (
SELECT CAST(
COLLECT(
sdo_util.get_coordinate(d.shape,level)
ORDER BY LEVEL
)
AS SDO_GEOMETRY_ARRAY
) AS point_array
FROM DUAL
CONNECT BY LEVEL <= sdo_util.getnumvertices(d.shape)
) p
ORDER BY
asset_id