Skip to Main Content
Forums

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How does SQL Developer select SDO_GEOMETRY properties as text?

User_1871May 4 2022 — edited May 4 2022

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  

image.pngBut 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.

Comments

unknown-7404
Answer 


I have read and watched videos regarding creating an Object of a class and calling its method in a different class. I'm still confused about how to properly do this. Using the code below can anyone explain how to properly call the objects method from my main.

Huh? You have NOT posted any 'main' or any 'objects method'.

If you need help with code you have to post the code.

The Java tutorials has dozens of trails on 'Classes and Objects': what they are, how to create them and how to use them.

https://docs.oracle.com/javase/tutorial/java/javaOO/classes.html

1. Create an instance of a class

2. call one or more of the public methods of that class

If the class has public static methods then you do NOT need to create an instance first.

I suggest you work your way thru those tutorials. They include WORKING example code.

Marked as Answer by 2801625 · Sep 27 2020
aJohny

As rp0428 suggested, please go through the tutorials first, add extra debug messages in the samples if needed, get an understanding how things works.

You have not given the full code, so we can't help much.

By looking at the content, it looks like you have written this in the Constructor. Refer the below link to understand about Constructors

https://docs.oracle.com/javase/tutorial/java/javaOO/constructors.html

If the above piece of code is the constructor code, it will get executed when you create the instance of the class itself (with those number of parameters);

ex:= FileContentsObject fileContentsObject = new FileContentsObject( cachecName, lastModifiedTimeStamp,contents, fileName, lines);

I have used the same variable names as the ones defined in the class, which is not necessary. These variables has to be defined first.

Hope it helps.

Cheers

AJ

2801625

Thanks for the advice so far this info has proved to be extremely helpful.

1 - 3

Post Details

Added on May 4 2022
#spatial, #sql-developer
7 comments
1,023 views
2 mentions