[GIS] Can’t select geometry from Oracle DB using SQL

enterprise-geodatabasegeometryoracle-dbmssqlst-geometry

I'm trying to select the geometry field from an ArcSDE feature class in an Oracle 11g database (inside of a Python script via PyODBC). This is my query:

SELECT SHAPE FROM USR.FEAT_CLASS

This is giving me error ORA-24359: OCIDefineObject not invoked for a Object type or Reference, and I get the same for any other feature class. If I select another non-geometry field the query works fine, so I know it isn't a problem with the script or connection string. Has anyone else encountered this problem?

UPDATE:
I have ArcGIS 9.3.1 installed locally but I'm not using it here, so I don't think that's playing a role. I'm not sure which version of ArcSDE is installed since I don't have regular access to the server, but I believe it's 9.3.1 as well. The Python script is using PyODBC to communicate directly with the Oracle server.

Best Answer

Just because Oracle supports the creation of custom objects in the database doesn't mean that all clients will be able to read their values. In this case, your PyODBC client doesn't know how to unpack the SDE.ST_GEOMETRY column type, so it hasn't told Oracle how it will handle geometry, and Oracle is generating the error saying it hasn't been told how to pass ST_GEOMETRY to the PyODBC driver.

There are a number of options open to you. The easiest are to request something the the ODBC client should be able to handle, like a CLOB or BLOB column:

SELECT sde.ST_AsText(shape) FROM usr.feat_class

-or-

SELECT sde.ST_AsBinary(shape) FROM usr.feat_class

ArcGIS 9.3.1 is sufficiently elderly to no longer be supported ("Retired" support status, as of Jan 2014). It's quite possible you may run into bugs in the SDE.ST_GEOMETRY implementation that have been fixed since install. At a minimum, you want to be sure that the terminal service pack (SP2) and various post-SP2 patches have been applied to the local Desktop install, to the application server binaries (if present), and to the listener support libraries that permit SQL*Plus spatial queries using SDE.ST_GEOMETRY.

Related Question