Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How does SQL Developer select SDO_GEOMETRY properties as text?

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.
Answers
-
I don't know if it helps, but to you know this tool: 3rd-party, free.
-
Could you explain what isn't working in SQL? I don't have 18C but in 19C same as in 12C I get same output in SQL:
SQL> select 2 sdo_geometry('LINESTRING (1 2,3 4)') as sdo_geom 3 from 4 dual 5 / SDO_GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES) -------------------------------------------------------------------------------- SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY( 1, 2, 3, 4)) SQL>
SY.
-
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
-
I'm actually using DB<>FIDDLE, not SQL Developer/a desktop SQL client.
DB<>FIDDLE doesn't output SDO_GEOMETRIES correctly. So I'm trying to figure out how to do it myself:
Top Answers: DB<>FIDDLE - Return a value/row when selecting SDO_GEOMETRY
-
Here's an answer on Stack Overflow that "writes out the content of an SDO_GEOMETRY type in a text format just like that used by SQLPLUS":
-
We have our own DB<>FIddle, and it's got 21c on it, not the de-supported 18c. If you're interested, that's livesql.oracle.com
-
Thanks. I wonder why Live SQL says the db is 19c?
Is there a way to get to a 21c version?