Tuesday 28 April 2015

Is ArcMap 10.0 Query Layer passing invalid SQL to Oracle 11gR2?


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 [2] 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 4326 with 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 [2]. 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.




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...