Custom aggregate function to collapse vertices to SDO_GEOMETRY

This builds the individual linestrings.

with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0  as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual 
)
SELECT asset_id,
       part_num,
       mdsys.sdo_geometry(
             3302,
             null,
             null,
             mdsys.sdo_elem_info_array(1,2,1),
             CAST(MULTISET( select case when r.rin = 1 then x
                                        when r.rin = 2 then y
                                        when r.rin = 3 then m
                                    end
                              from cte b,
                                   (select level rin from dual connect by level < 4) r
                            where b.asset_id = a.asset_id
                              and b.part_num = a.part_num
                            order by b.vertex_num, r.rin
                          ) as mdsys.sdo_ordinate_array 
            ) 
        ) as geom
  from cte a
  group by asset_id, part_num
  order by part_num;

Note how the X, Y and M ordinates are "serialised" into an array (of type mdsys.sdo_ordinate_array) using the MULTISET operator. Result is:

  ASSET_ID   PART_NUM GEOM
---------- ---------- ----
         1          1 SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54))
         1          2 SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54))

Creating a multilinestring involves aggregating the linestrings using the asset_id attribute.

with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0  as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual 
)
SELECT asset_id,
       SDO_AGGR_UNION(SDOAGGRTYPE(geom,0.005)) as mGeom
  FROM (SELECT asset_id,
               part_num,
               mdsys.sdo_geometry(
                     3302,
                     null,
                     null,
                     mdsys.sdo_elem_info_array(1,2,1),
                     CAST(MULTISET( select case when r.rin = 1 then x
                                                when r.rin = 2 then y
                                                when r.rin = 3 then m
                                            end
                                      from cte b,
                                           (select level rin from dual connect by level < 4) r
                                    where b.asset_id = a.asset_id
                                      and b.part_num = a.part_num
                                    order by b.vertex_num, r.rin
                                  ) as mdsys.sdo_ordinate_array 
                    ) 
                ) as geom
          from cte a
          group by asset_id, part_num
          order by part_num
        ) f
  GROUP BY asset_id;

Result:

  ASSET_ID MGEOM
---------- -----
         1 SDO_GEOMETRY(3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))

See also my article [Building linestrings from GPX GPS data]: https://www.spdba.com.au/loading-and-processing-gpx-1-1-files-using-oracle-xmldb-2/

You can concatenate the it into a multi-line string of parts and then generate the SDO_GEOMETRY from that string:

SELECT asset_id,
       SDO_GEOMETRY(
         'MULTILINESTRING (' || LISTAGG(part, ',') WITHIN GROUP (ORDER BY part_num) || ')'
       ) AS geom
FROM   (
  SELECT asset_id,
         part_num,
         '(' || LISTAGG(x || ' ' || y || ' ' || m, ',') WITHIN GROUP (ORDER BY vertex_num) || ')'
           AS part
  FROM   vertices
  GROUP BY asset_id, part_num
)
GROUP BY asset_id

db<>fiddle here

Create a type to store the point:

CREATE TYPE PointLRS AS OBJECT(
  X NUMBER,
  Y NUMBER,
  M NUMBER
);

Then create a user-defined aggregation type:

CREATE TYPE Line3DAggType AS OBJECT(
  ordinates SDO_ORDINATE_ARRAY,

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx  IN OUT Line3DAggType
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT Line3DAggType,
    point IN     PointLRS
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Line3DAggType,
    returnValue    OUT SDO_GEOMETRY,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Line3DAggType,
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY Line3DAggType
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := Line3DAggType( SDO_ORDINATE_ARRAY() );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self  IN OUT Line3DAggType,
    point IN     PointLRS
  ) RETURN NUMBER
  IS
  BEGIN
    IF     point IS NOT NULL
       AND point.X IS NOT NULL
       AND point.Y IS NOT NULL
       AND point.M IS NOT NULL
    THEN
      self.ordinates.EXTEND(3);
      self.ordinates(self.ordinates.COUNT - 2) := point.X;
      self.ordinates(self.ordinates.COUNT - 1) := point.Y;
      self.ordinates(self.ordinates.COUNT - 0) := point.M;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT Line3DAggType,
    returnValue    OUT SDO_GEOMETRY,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF self.ordinates.COUNT > 0 THEN
      returnValue := SDO_GEOMETRY(
        3302,
        NULL,
        NULL, 
        SDO_ELEM_INFO_ARRAY(1,2,1),
        self.ordinates
      );
    ELSE
      returnValue := NULL;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT Line3DAggType,
    ctx         IN OUT Line3DAggType
  ) RETURN NUMBER
  IS
  BEGIN
    FOR i IN 1 .. ctx.ordinates.COUNT LOOP
      self.ordinates.EXTEND;
      self.ordinates(self.ordinates.COUNT) := ctx.ordinates(i);
    END LOOP;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

Then define a custom aggregation function:

CREATE FUNCTION Line3DAgg( point PointLRS )
RETURN SDO_GEOMETRY
PARALLEL_ENABLE AGGREGATE USING Line3DAggType;
/

Then you can aggregate the points for each part into a line and then concatenate the lines:

SELECT asset_id,
       SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(part, 0.005)) AS geom
FROM   (
  SELECT asset_id,
         part_num,
         Line3DAgg(PointLRS(x, y, m)) AS part
  FROM   vertices
  GROUP BY asset_id, part_num
)
GROUP BY asset_id

db<>fiddle here