Centroid querying errors to Oracle database

EricSpitler — Sun, 12 Dec 2021 01:26:34 GMT

I am attempting to create a query layer in ArcMap 10 to an Oracle database, using the centroid as the geometry for the data. I can't post the actual query, but here is a generalization:

SELECT TO_CHAR(F.ID) "Unique ID", SDO_Geom.SDO_Centroid(S.Geometry,1) "Centroid"
FROM MyTable F, AnotherTable S
WHERE F.ID = S.MyTable_ID

Arc is able to validate the query and determime that the geometry type is a Point with unknown spatial reference. I then set the reference to WGS84 (leaving as Unknown doesn't help) and save the query layer. As soon as it attempts to refresh the data, i get the following error:

One or more layers failed to draw:

MyQuery: Underlying DBMS error [ORA-06553:
PLS-306: wrong number or types of arguments in call to
'OGC_CENTROID'
]

I've run the SQL in SQLDeveloper and the data returns perfectly, so the syntax is correct. Why does Arc call 'OGC_CENTROID', and is this the reason for the query failing? Is there a different Oracle-compatible centroid function that Arc supports?

Re: Centroid querying errors to Oracle database

EricSpitler — Tue, 14 Feb 2012 15:56:34 GMT

I just attempted the query w/o the centroid function (i.e. just pulling the geometry) and it still returned an error about the centroid. Example geometry returned in SQLDeveloper:

Geometry:

MDSYS.SDO_GEOMETRY(2003,8307,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(121.54331,42.109625,121.543454,41.992575,121.700346,41.992575,121.70049,42.109625,121.54331,42.109625))

Centroid:

MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(121.6219,42.0511271376152,null),null,null)

topic Centroid querying errors to Oracle database in Data Management Questions

Centroid querying errors to Oracle database

Re: Centroid querying errors to Oracle database