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 )