0

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

User1974
  • 276
  • 1
  • 17
  • 63

1 Answers1

1

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
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • 1
    Char Col... :-D – Sayan Malakshinov May 04 '22 at 01:25
  • For my notes, for other SDO_GEOMETRY object attributes, such as `.sdo_ordinates`, I need to investigate whether the ordinates get ordered correctly or not: *"[I am not sure if your simplified query keeps the order of the ordinates. To be safe I would definitely use the ORDER BY clause of LISTAGG.](https://community.oracle.com/tech/developers/discussion/comment/16834549/#Comment_16834549)"* – User1974 May 04 '22 at 14:47