Error selecting geometry from Oracle DB with ArcSDE

Question

I'm trying to select a geometry column as well-known text from a feature class in an Oracle 11g/ArcSDE 10.1 geodatabase. (This is in a Python script using pypyodbc as a driver). This is my SQL:

SELECT SDE.ST_AsText(SHAPE) FROM OWNER.FEAT_CLASS

When I run this I get a few Oracle errors:

ORA-29900: operator binding does not exist\nORA-06553: PLS-306: wrong number or types of arguments in call to 'ST_ASTEXT

I found this support article which says to fully qualify all references to ST_GEOMETRY operators, but I have the SDE prefix so I'm not sure what's missing. Does anyone have any ideas regarding this?

UPDATE: If I just select the SHAPE field without any SDE functions I get Decimal('214'). Interestingly, that's also the object ID.

• Russell at ISC: Not an SDE user, but from other spatial DBs I'd suggest trying SHAPE.ST_AsText()
• Vince: Is the geometry column using SDE.ST_GEOMETRY or SDELOB/SDEBINARY storage? SDE.ST_AsText only works with a SDE.ST_GEOMETRY storage (and only if the libst_shapelib.so / st_shapelib.dll is registered with EXTPROC).
• bertday: Stupid question but - how can I tell? Is the type stored in an SDE table somewhere? If I just describe the table the SHAPE field shows up as Decimal, which I know is not the case.
• Vince: Yes, it's buried in a bitmask, but the easiest way is describe it in SQL*Plus -- it will show an SDE.ST_GEOMETRY datatype (or NUMBER(38) if SDELOB/SDEBINARY). Some other display mechanism might well show "Decimal" for a NUMBER(38) type. The only way to get WKT out of an SDELOB/SDEBINARY column is to use the ArcSDE API (or decode & encode on your own, based on a join to the Fn table).
• bertday: Sure enough it was SDELOB... Could you elaborate on what the Fn table is?

Answer 1

In order to find out the Fn-Table where the actual points are stored, you can

select object_id from sde.column_registry where table_name = 'MYTABLE' and column_name = 'SHAPE';

The geometry will be stored in the table Fxxxx with xxxx = object_id.