Function to convert varray to delimited string

User_1871

I have VARRAYS in SDO_GEOMETRY objects:

create table test_table (shape sdo_geometry);

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)'));

select
    (shape).sdo_ordinates as sdo_ordinate_array
from
    test_table


SDO_ORDINATE_ARRAY
------------------
MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60)
MDSYS.SDO_ORDINATE_ARRAY(70, 80, 90, 100)
MDSYS.SDO_ORDINATE_ARRAY(110, 120, 130, 140, 150, 160, 170, 180)

I want to extract the ordinates from the VARRAYS as comma delimited strings:

ORDINATES
------------------------------
10,20,30,40,50,60
70,80,90,100
110,120,130,140,150,160,170,180

It would be helpful if there were a function that could convert the VARRAYS to strings.

There are ways to do it in a query. But I would prefer to do it with a function since that would be a lot more succinct.

Something like this:

VARRAY_TO_VARCHAR2( varray )

User_3ABCE

select json_array(t.x.sdo_ordinates returning clob) str
from (select sdo_geometry('linestring(10 20, 30 40, 50 60)') x from dual) t;

STR                
-------------------
[10,20,30,40,50,60]