# 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.

-
Which Service Pack are you on in ArcMap 10.0? –  Mintx Nov 15 '12 at 22:29
@Mintx SP3 . . . . . . –  tomfumb Nov 15 '12 at 23:14

## 2 Answers

I have been all up and down this block.
Several years ago I had a similar problem with the difference between the esri state plane crs and the same in oracle sdo.
It came down to converting my sdo ordinate array into JUST points.
I had a bud at oracle that wrote me a little function to do it for me.
I am not at that job anymore but might still have a copy. I'll take a look.
I can't remember much abou the problem but here is a later thread that has some hints in it.
OTN spatial forum thread
I may have posted it to OTN spatial forum. You might search there.
If I find it I will post back here.

-
ok thanks but I'm a little confused - as far as I'm aware the sdo_ordinate_array sent from ArcMap will only ever be a rectangle (indicated by 1003), so I shouldn't hit the ordinate count limit you referenced?? –  tomfumb Oct 3 '12 at 1:22
I had several problems trying to set up an OGC (SDO) geodatabase and can't remember the exact error you have. However the scenario is very similar. I had problems with the esri "named" coordinate system, and the match in oracle. I think one of my fixeds was to go into the oracle metadata and fix the crs. I pounded my problem on OTN and then an esri person found it there. I got great help from teir 2 esri and Oracle spatial programmers (non-support) –  Brad Nesom Oct 3 '12 at 13:40
I'm not sure I have the option of changing Oracle's 4326 CRS definition, there're too many things it could break. Also you mention this is several years ago so it can't have been with ArcMap Query Layers (introduced at 10.0), presumably SDE was involved somewhere in the process? I'm inclined to think this is probably a different issue, although so far it's the only suggestion –  tomfumb Oct 3 '12 at 16:33

ArcGIS uses the spatial reference identifier (SRID) specified on the shape values in the database to determine the spatial reference of the features being returned by a query layer. There may be instances where ArcGIS cannot accurately determine the spatial reference based on the current SRID value set on those features in the database. In these cases, the query layer will have an unknown spatial reference. It is possible to manually define the spatial reference on the query layer on the Advanced Options dialog box. It is important to note that this does not reproject the query layer; it just defines what spatial reference should be used when mapping the results of the query within ArcGIS.

Check the Advanced Options check box on the New Query Layer dialog box.
Click Select to choose the coordinate system to be used for the spatial reference.
Optionally, you can import the spatial reference from an existing dataset by clicking Import.

-
I'll try and look at this tomorrow when I have access to the machine again. Thanks for the suggestion –  tomfumb Oct 11 '12 at 16:52
don't think I have a chance to test before the bounty goes (access issues, not laziness), I'll set up a new one if this solves my problem. –  tomfumb Oct 16 '12 at 19:06
Unfortunately this doesn't help the problem –  tomfumb Oct 25 '12 at 19:03