Oracle 18c

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

CREATE TYPE vertex_type_cust AS object
(
  x  NUMBER,
  y  NUMBER,
  z  NUMBER,
  w  NUMBER,
  id NUMBER );
CREATE TYPE vertex_set_type_cust AS TABLE OF vertex_type_cust;
CREATE OR replace FUNCTION getvertices_cust(geometry mdsys.sdo_geometry)
  RETURN vertex_set_type_cust
IS
  i      NUMBER;
  dims   NUMBER;
  coords NUMBER;
  result vertex_set_type_cust;
  dim mdsys.sdo_dim_array;
  is_zero BOOLEAN;
  etype   NUMBER;
BEGIN
  result := vertex_set_type_cust();
  -- handle the POINT case here
  IF (geometry.sdo_ordinates IS NULL) THEN
    result.extend;
    result(1) := vertex_type_cust(geometry.sdo_point.x, geometry.sdo_point.y, geometry.sdo_point.z,NULL,1);
    RETURN result;
  END IF;
  -- all other cases here
  coords := geometry.sdo_ordinates.count;
  dims := geometry.get_dims;
  IF (dims = 0) THEN
    RETURN result;
  END IF;
  coords := coords/dims;
  FOR i          IN 0 .. coords-1
  LOOP
    result.extend;
    IF (dims = 2) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(2*i+1), geometry.sdo_ordinates(2*i+2), NULL,NULL,i+1);
    ELSIF (dims = 3) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(3*i+1), geometry.sdo_ordinates(3*i+2), geometry.sdo_ordinates(3*i+3) ,NULL,i+1);
    ELSIF (dims = 4) THEN
      result(i+1) := vertex_type_cust(geometry.sdo_ordinates(4*i+1), geometry.sdo_ordinates(4*i+2), geometry.sdo_ordinates(4*i+3), geometry.sdo_ordinates(4*i+4), i+1);
    END IF;
  END LOOP;
  RETURN result;
END;
/
create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);
insert into a_sdo_geometry_tbl (line_id, shape) 
values (1, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671539.6852734378,4863324.181436138, 671595.0500703361,4863343.166556185, 671614.013553706,4863350.343483042, 671622.2044153381,4863353.525396131))  );
1 rows affected

insert into a_sdo_geometry_tbl (line_id, shape) 
values (2, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (71534.5567096211,4863119.991809748, 671640.7384688659,4863157.132745253, 671684.8621150404,4863172.022995591))  );
1 rows affected

insert into a_sdo_geometry_tbl (line_id, shape) 
values (3, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671622.2044153381,4863353.525396131, 671633.3267164109,4863357.846229106, 671904.0614077691,4863451.286166754))  );
1 rows affected

insert into a_sdo_geometry_tbl (line_id, shape) 
values (4, sdo_geometry (2002, null, null, sdo_elem_info_array (1,2,1), 
    sdo_ordinate_array (671684.8620521119,4863172.022995591, 671892.1496144319,4863244.141440067, 671951.2156571196,4863264.824310392, 671957.4471461186,4863266.847617676, 671966.8243856924,4863269.146632658))  )
1 rows affected

select
    a.line_id,
    b.id as vertex_id,
    b.x, 
    b.y
from
    a_sdo_geometry_tbl a
cross join
    table(getvertices_cust(a.shape)) b
order by 
    a.line_id, b.id;


LINE_ID
VERTEX_ID
X
Y


1
1
671539.6852734378
4863324.181436138

1
2
671595.0500703361
4863343.166556185

1
3
671614.013553706
4863350.343483042

1
4
671622.2044153381
4863353.525396131

2
1
71534.5567096211
4863119.991809748

2
2
671640.7384688659
4863157.132745253

2
3
671684.8621150404
4863172.022995591

3
1
671622.2044153381
4863353.525396131

3
2
671633.3267164109
4863357.846229106

3
3
671904.0614077691
4863451.286166754

4
1
671684.8620521119
4863172.022995591

4
2
671892.1496144319
4863244.141440067

4
3
671951.2156571196
4863264.824310392

4
4
671957.4471461186
4863266.847617676

4
5
671966.8243856924
4863269.146632658

LINE_ID	VERTEX_ID	X	Y
1	1	671539.6852734378	4863324.181436138
1	2	671595.0500703361	4863343.166556185
1	3	671614.013553706	4863350.343483042
1	4	671622.2044153381	4863353.525396131
2	1	71534.5567096211	4863119.991809748
2	2	671640.7384688659	4863157.132745253
2	3	671684.8621150404	4863172.022995591
3	1	671622.2044153381	4863353.525396131
3	2	671633.3267164109	4863357.846229106
3	3	671904.0614077691	4863451.286166754
4	1	671684.8620521119	4863172.022995591
4	2	671892.1496144319	4863244.141440067
4	3	671951.2156571196	4863264.824310392
4	4	671957.4471461186	4863266.847617676
4	5	671966.8243856924	4863269.146632658