In two different environments I'm having trouble using ArcMap 10.0 Query Layers to view SDO_GEOMETRY (point) data in Oracle 11.2. This suggests I'm doing something wrong...
Points are in Oracle stored under SDO_GEOMETRY, 2001 type (2D point), 4326 SRID (WGS84 lat/lon). The table / column has a spatial index and is entered in user_sdo_geom_metadata.
I can successfully query the table in a way that uses its spatial index (SDO_WITHIN_DISTANCE), so I know this is functional.
When I add the table to ArcMap as a query layer the query validates fine, however nothing is displayed when I zoom to the layer. I have verified that the zoom extent is correct. If I try to show labels I see a drawing error:
Underlying DBMS error[ORA-29902: error in executing ODCIIndexStart() routine ORA-22060: argument  is an invalid or uninitialized number ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333 ]
Not understanding how this involves me I decided to trace the database.
ArcMap makes the following query to Oracle:
select LOCATION_GEOMETRY from (select * from TEST.BC_POINT_TEST_1) a where sdo_filter(LOCATION_GEOMETRY,sdo_geometry( to_blob( :i1 ), :i2),'querytype=window') = 'TRUE'
I filled in the blanks to create the following query:
select LOCATION_GEOMETRY from (select * from TEST.BC_POINT_TEST_1) a where sdo_filter(LOCATION_GEOMETRY, SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(53.276, 23.232, 53.587, 23.451)),'querytype=window') = 'TRUE'
Which worked fine. However, if I replace
NULL it throws a similar error (but not exactly the same) to what I'm seeing in ArcMap. This makes me think possibly ArcMap isn't passing the correct SRID for Oracle to know what to do. I set the data frame's coordinate system to WGS84 but this didn't make a difference.
The error reported through ArcMap references
argument . If this is actually the second bind parameter in the traced query that would be the
SDO_ORDINATE_ARRAY, but as this is an array it would make no sense to complain that this is
an invalid or uninitialized number.
If anyone has prior experience with this issue or suggestions on where to take it I would be most appreciative!
Edit: so far nothing has helped solve this problem, but I recently had an opportunity to test with 10.1 and everything worked as expected. This suggests there's nothing wrong with the Oracle instance.