Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Function to convert varray to delimited string

User_1871Jul 10 2022

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 )

Comments
Post Details
Added on Jul 10 2022
1 comment
233 views