In two different environments I'm having trouble using ArcMap 10.0 SP3 Query Layers to view SDO_GEOMETRY (point) data in Oracle 11.2 (11gR2).

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:

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:

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.

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.


2 Answers 2


You could try manually defining the spatial reference of a query layer :

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.
  • Unfortunately this doesn't help the problem
    – tomfumb
    Oct 25, 2012 at 19:03

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, 2012 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, 2012 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, 2012 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.