4

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.

1 Answer 1

3

You can use Query Layers in ArcMap to display Oracle Spatial geometries and data, however I do not think an equivalent exists for ArcGIS Server (would be thrilled to be proven wrong, though!).

3
  • 2
    from the comments on that link: "You will be able to use this feature to add a layer to ArcMap which has a native SDO_Geometry column storing spatial information for the feature. This map document can then be published as an ArcGIS Server map service. In this way sdo_geometry information will be exposed through a web service." I'll post back if I get anywhere
    – tomfumb
    Aug 22, 2012 at 18:38
  • Oh cool, let us know how it goes!
    – blah238
    Aug 22, 2012 at 18:44
  • 2
    Service works fine through AGS, the only issue I had was the MXD doesn't travel well - even though the server machine's ArcMap had a connection with the same name I had to recreate the layers. Editing isn't supported through Query Layers so you have to edit through SQL. For my purposes this isn't such a problem though.
    – tomfumb
    Aug 22, 2012 at 23:33

Your Answer

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

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