how to get the lat and long from feature table in Oracle DB

[DRae]

Hi I heard that in MS SQL, we can do the following

select shape, shape.STX as LONG, shape.STY as LAT from sde.fc

If my Geodatabase is in Oracle, and how can I get the STX and STY from the shape column. I tried the above and it return 

ORA-00904: "SHAPE"."STX": invalid identifier
00904. 00000 - "%s: invalid identifier"

[George_Thompson · Accepted solution]

If you have ST_Geometry configured (https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/configure-oracle-extpr...) you could use ST functions to do this: https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-oracle/stgeometry-oracle.htm (reference doc: https://pro.arcgis.com/en/pro-app/latest/help/data/databases/pdf/stgeometry_reference.pdf - look at p336 (ST_X / ST_Y).

That would be where I would start.

[DRae]

Thanks. I am able to do as below

select sde.st_x(shape) from fc

[VinceAngelo]

Note that you should never store user data in the SDE schema, so the "from sde.fc" part is a problem. Best practice is to create a new login to own spatial data (and a new tablespace to store the data for that login).  Using the SDE login for this is akin to using SYSTEM or DBO for noodling, or web surfing the dark net as administrator -- It's not likely to end well.

- V