I have an Oracle 11g database setup with Oracle Locator.
I have created a table with the this query:
CREATE TABLE geotest (id NUMBER(38) NOT NULL, shape SDO_GEOMETRY);
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.