By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table test_table (shape sdo_geometry);
begin
insert into test_table (shape) values (sdo_geometry('linestring(10 20, 30 40, 50 60)'));
insert into test_table (shape) values (sdo_geometry('linestring(70 80, 90 100)'));
insert into test_table (shape) values (sdo_geometry('linestring(110 120, 130 140, 150 160, 170 180)'));
end;
/
1 rows affected
select
(shape).sdo_ordinates as sdo_ordinate_array
from
test_table
select
ordinates
from
test_table t
cross join lateral (
select listagg(column_value, ',') within group (order by rownum)
as ordinates
from table(t.shape.sdo_ordinates)
) s
| ORDINATES |
|---|
| 10,20,30,40,50,60 |
| 70,80,90,100 |
| 110,120,130,140,150,160,170,180 |
with function ordinates_to_varchar2(shape in sdo_geometry) return varchar2
deterministic is
v_ordinates varchar2(32767);
begin
select
ordinates into v_ordinates
from
test_table t
cross join lateral (
select listagg(column_value, ',') within group (order by rownum)
as ordinates
from table(t.shape.sdo_ordinates)
) s;
return v_ordinates;
end;
select
ordinates_to_varchar2(shape)
from
test_table
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 15