SDE.ST_GEOMETRY for PostgreSQL has an ST_GeomFromCollection function:
--Return each linestring in the multilinestring
SELECT sde.st_astext((sde.st_geomfromcollection(gst.shape)).st_geo) shapetext, ((sde.st_geomfromcollection(gst.shape)).path[1]) path
FROM ghanasharktracks gst;
shapetext path
-----------------------------------------------------------------------------------------------------------
"LINESTRING Z ( 1.00000000 1.00000000 0.00000000, 1.00000000 6.00000000 0.00000000)" 1
"LINESTRING Z ( 1.00000000 3.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)" 2
"LINESTRING Z ( 3.00000000 1.00000000 0.00000000, 3.00000000 3.00000000 0.00000000)" 3
"LINESTRING Z ( 4.00000000 1.00000000 0.00000000, 4.00000000 6.00000000 0.00000000)" 4
It would be really helpful if we had the same functionality in Oracle. For example, it would simplify this query: Select feature parts and vertices as rows (ST_GEOMETRY)
I don't see why that couldn't be done in Oracle. The return type could be a nested table. In the FROM clause, we could cross join to the nested table to get the feature parts.
Related:
Cross-joining with a table object propagates rows (without needing Table() function)
At what version did the TABLE() keyword for table collection expressions become optional?
Cross join to varray without using Table() expression
Could Esri consider creating an ST_GeomFromCollection function for Oracle?