By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with cte (shape) as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual --2D: (X,Y)
)
select c.multiline_num,
e.part_no,
sdo_util.to_wktgeometry(e.elem)
from ( SELECT ROWNUM AS multiline_num,
shape
FROM cte
) c
CROSS JOIN LATERAL (
SELECT LEVEL AS part_no,
SDO_UTIL.EXTRACT(c.shape, LEVEL) AS elem
FROM DUAL
CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(c.shape)
) e;
| MULTILINE_NUM | PART_NO | SDO_UTIL.TO_WKTGEOMETRY(E.ELEM) |
|---|---|---|
| 1 | 1 | LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) |
| 1 | 2 | LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) |
| 2 | 1 | LINESTRING (5.0 5.0, 6.0 6.0) |
| 2 | 2 | LINESTRING (7.0 7.0, 8.0 8.0) |
| 2 | 3 | LINESTRING (9.0 9.0, 0.0 0.0) |
| 3 | 1 | LINESTRING (1.0 1.0, 2.0 2.0) |
with cte (shape) as (
--Each part is wrapped in brackets and separated by commas: (p1),(p2)
select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all --3D: (X,Y,Z)
select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all --2D: (X,Y)
select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual --2D: (X,Y)
)
select c.multiline_num,
e.part_no,
sdo_util.to_wktgeometry(e.elem)
from ( SELECT ROWNUM AS multiline_num,
shape
FROM cte
) c
CROSS JOIN LATERAL (
SELECT ROWNUM AS part_no,
a.object_value AS elem
FROM TABLE(SDO_UTIL.EXTRACT_ALL(c.shape)) a
) e;
| MULTILINE_NUM | PART_NO | SDO_UTIL.TO_WKTGEOMETRY(E.ELEM) |
|---|---|---|
| 1 | 1 | LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) |
| 1 | 2 | LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) |
| 2 | 1 | LINESTRING (5.0 5.0, 6.0 6.0) |
| 2 | 2 | LINESTRING (7.0 7.0, 8.0 8.0) |
| 2 | 3 | LINESTRING (9.0 9.0, 0.0 0.0) |
| 3 | 1 | LINESTRING (1.0 1.0, 2.0 2.0) |