Forum Stats

  • 3,874,901 Users
  • 2,266,788 Discussions
  • 7,911,999 Comments

Discussions

Function to convert varray to delimited string

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_1871
1 votes

Active · Last Updated

Comments

  • User_3ABCE
    User_3ABCE Member Posts: 204 Silver Badge
    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]
    
    User_1871