I'm interested to see if anyone has successfully got this setup working, and if so how many sacrifices were required and to which deities.
I am hoping to ultimately get ArcGIS Server 10 serving map services with Oracle 10g data without involving SDE at all. Right now I'm just trying to get ArcMap 10 to show me some data. Based on ESRI's documentation I believe this should be possible but the docs don't cover the problems I'm having.
My machine has the Oracle 11gR2 client and this is reportedly compatible with 10g. All machines involved are 32-bit only, so the 64-bit specific Oracle connection problems don't concern me (yet). I can sqlplus to the DB with the same connection parameters.
My table looks like this:
create table arc_ora_test_2 (
m_id int primary key,
attr_1 int,
attr_2 int,
shape SDO_GEOMETRY
);
INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('arc_ora_test_2', 'shape',
SDO_DIM_ARRAY
(SDO_DIM_ELEMENT('LONG', -180.0, 180.0, 0.0000005),
SDO_DIM_ELEMENT('LAT', -90.0, 90.0, 0.0000005)),
4326);
CREATE INDEX arc_ora_test_2_spatial_idx_1
ON arc_ora_test_2(shape)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
From what I can gather, in ArcCatalog 'Add Spatial Database Connection' is exclusively for SDE-enabled databases, so I'm not using this. For the OLE DB connection I've tested both the Microsoft OLE DB for Oracle and Oracle OLE DB providers. Both succeeed the test connection to the DB. However...
Microsoft OLE DB for Oracle connection cannot list available tables, reporting 'General Function Failure' Oracle OLE DB connection can list tables, but when I try to render the data I am told 'An invalid SQL statement was used'
This second (Oracle provider) connection gets furthest but it seems that ArcMap is not writing SQL properly, which is odd as there are no definition queries or other filters - this is simply a 'show me everything' query which presumably has a bounding box in the where clause.
Is it possible that the existence of the 11gR2 client is tricking ArcMap into thinking it needs to write 11gR2-compatible queries? Is there any significant difference between 10g and 11gR2 for spatial queries?
Any thoughts on this dilemma would be very much appreciated.