I have a Oracle 18c query that outputs an SDO_GEOMETRY object:
select
sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom
from
dual
That works as expected in SQL Developer:
CTRL+F5 Output: [MDSYS.SDO_GEOMETRY]
F5 Output: SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4))
Question: How does SQL Developer convert the SDO_GEOMETRY object properties to text? What code or functions does it use?
The reason I ask:
I have a use case where I want to output the SDO_GEOMETRY properties...the same way that SQL Developer does (when I hit F5).
But I haven't found a clean way to do that using SQL. There doesn't seem to be an OOTB function for it.
I cobbled together a query that more-or-less works:
select
'MDSYS.SDO_GEOMETRY(' ||
a.shape.sdo_gtype || ', ' ||
nvl(to_char(a.shape.sdo_srid), 'NULL') || ', ' ||
NVL2(
a.shape.sdo_point,
'MDSYS.SDO_POINT_TYPE ('
|| COALESCE(TO_CHAR(a.shape.sdo_point.X), 'NULL') || ', '
|| COALESCE(TO_CHAR(a.shape.sdo_point.Y), 'NULL') || ', '
|| COALESCE(TO_CHAR(a.shape.sdo_point.Z), 'NULL')
|| ')',
'NULL'
) || ', ' ||
'MDSYS.SDO_ELEM_INFO_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_elem_info)) || '), ' ||
'MDSYS.SDO_ORDINATE_ARRAY(' || (select listagg(column_value,', ') from table(a.shape.sdo_ordinates)) || '))'
as sdo_geom_properties
from
(
select
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(1, 2, 3, 4)) as shape
from
dual
union all
select
SDO_GEOMETRY(2001, NULL, SDO_POINT_TYPE(-79, 37, NULL), NULL, NULL) as shape
from
dual
) a
But that's not as simple or robust as I'd like it to be.
How does SQL Developer do it? Could I do something similar to what SQL Developer does, but in a query?
I'm aware that we can convert SDO_GEOEMTRY to other formats like WKT or JSON. But in this case, I would prefer to output the raw SDO_GEOMETRY properties, if possible.