Can't select geometry from Oracle DB using SQL

Question

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.

• Vince: What version of ArcGIS do you have installed? How are you executing this query? With which connection interface? With what object(s)? Please update the question.
• bertday: Hi Vince, I edited the question with some more comments. Thank you.
• Vince: You're not using an ST_GEOMETRY-aware client, so it doesn't know how to handle the custom object. Convert to Well-Known Text or Well-Known Binary in the query instead.

Answer 1

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.

• bertday: This was a huge help, thank you. I'm now getting a different error (Invalid DLL Path\nORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG"), but I suspect this is because one of the patches is missing as you suggested. I will keep playing around with it.
• Vince: No, that error indicates that the EXTPROC is not properly configured - support.esri.com/en/knowledgebase/techarticles/detail/39119