sqloracle-databaseoracle18coracle-spatialvarray

Convert VARRAY to text (for concatenation)

I have a query that outputs a VARRAY:

select
    a.sdo_geom.sdo_elem_info
from
    (
    select
        sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
    from
        dual
    ) a

In SQL Developer, the VARRAY gets output as text:

enter image description here

Question:

Similar to what SQL Developer does, is there a way to convert the VARRAY to text using SQL — so that I can concatenate the value in a string?

Example: (fails)

select
    'MDSYS.SDO_ELEM_INFO_ARRAY(' || a.sdo_geom.sdo_elem_info || ')'
from
    (
    select
      sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
    from
      dual
    ) a

[Desired result: 'MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1)']

ORA-00932: inconsistent datatypes: expected CHAR got MDSYS.SDO_ELEM_INFO_ARRAY
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
*Cause:    
*Action:
Error at Line: 2 Column: 37

Just a heads up:

DB<>FIDDLE doesn't seem to output VARRAYS correctly. It outputs an empty resultset, which can be confusing. Return a value/row when selecting SDO_GEOMETRY

Solution

  • You can use listagg in a subquery:

    DBFiddle: https://dbfiddle.uk/?rdbms=oracle_21&fiddle=fa79482bb22501fd67fb2c498ff90bf9

    select
    (select listagg(column_value,',') from table(a.sdo_geom.sdo_elem_info)) cc
from
    (
    select
        sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
    from
        dual
    ) a