I have an Oracle 11g database setup with Oracle Locator.

I have created a table with the this query:


Then I inserted one point with the this query:

INSERT INTO geotest VALUES(1,SDO_GEOMETRY(2001,3395,SDO_POINT_TYPE(-111.949439,40.722283,NULL),NULL,NULL));

The result is no data in the query layer in ArcMap. It allows me to create the layer, and validates. But then it makes me go through the advanced properties which I suspect means it can't see any of the rows to automatically pull the SRID.

Any guidance would be appreciated!

Update: So when I insert the metadata into the table using this:

insert into user_sdo_geom_metadata values ('geotest','shape',sdo_dim_array(sdo_dim_element('X',-180,180,0.005),sdo_dim_element('Y',-180,180,0.005)),4326);

And creating the spatial index, I can see the following point inserted into the database using this query:

insert into geotest values(1,'test',sdo_geometry(2001,4326,sdo_point_type(0,0,0),null,null));

So it would appear that a point with all 0s work, but anything beside that doesn't. I haven't found anything in the documentation that relates to this, or even any users complaining about that. Any either tricks that may be useful?

Update 2: Alright it appears that after I drop the index, and create it again, the points then show up. I am not an Oracle expert, so this confuses me, anyone have an explanation for this behavior? I thought once an index was created, as soon as a point is inserted, the index updates.

  • Does Oracle require that all geometries in a table have the same SRID? Commented Sep 16, 2011 at 16:10
  • @Kitk yup they do for the spatial index to work
    – Erik L
    Commented Sep 23, 2011 at 18:49
  • and a metadata table record is required also. Just saw this answer below. perhaps the spatial index is not sufficient.
    – Brad Nesom
    Commented Sep 24, 2011 at 5:53

3 Answers 3


I haven't used the Query layer but I assume it needs to be a valid oracle layer? Which means you would have to add an entry for the layer to the user_sdo_geom_metadata table (see here) and also creating a spatial index (see there)

  • That looks like one thing I was missing. I did all those steps, and can display a point at 0,0,0 but no where else.
    – Erik L
    Commented Sep 23, 2011 at 18:53
  • I just noticed that you define the Y dimension between -180, 180. Shouldn't that be -90,90? Probably this is where Oracle is getting confused since the SRID is 4326. Other than that, I haven't seen this behavior before. When you add new shapes the spatial index is updated automatically
    – mapoholic
    Commented Sep 24, 2011 at 16:32

The first insert is using SRID 3395 (mercator), but the coords you are passing look suspiciously like lat/longs. The second insert you're passing 4326 (which is WGS84).

Did you try the second insert with lat/long values and SRID 4326?


If the query layer is in the same coordinate system as the view, then it should still display.

Unfortunately, the biggest draw-back (IMO) of query layers is that the source geometry needs to have a spatial index to display. There are all sorts of derived queries I would love to do on the Oracle side, and the query layer will do them, but the results don't show. For example, Oracle's linear referencing on the fly. "Flashing" a given record in the table will display the geometry briefly, which proves it's there.

You can export the query layer and then you will get the results. Of course, that isn't optimal.

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.