I have a
sde.st_geometry line feature class called
ROADS. I've created a view using a
st_geometry function called
st_startpoint which returns an
st_point is a subclass of
st_geometry is the superclass.
SELECT OBJECTID, SDE.ST_STARTPOINT(SHAPE) AS START_POINT FROM ENG.ROAD
Unfortunately, there is a known Oracle bug that is preventing me from using my view (which includes the
st_point subclass column): Bug: Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle.
The bug description states:
...the binding used in Oracle to fetch the geometry binds an st_geometry type. Because the attribute or values returned from the attribute do not map to st_geometry, the Oracle internal error is encountered. By definition, a type binding should support its subtype definitions. This limitation is currently a bug within Oracle's server.
The workaround, if you can call it that, is:
Convert the geometry attribute field from the subtype (for example, st_point) to the supertype st_geometry.
However, it doesn't say how to do the conversion; I don't know how to convert from a user-defined type subclass to a user-defined type superclass. I've thoroughly searched the
st_geometry functions, and I don't believe there is a function that will do this. Possibly because, in theory, this scenario shouldn't exist. I ought to be able to add an
st_point column to ArcMap, because it is
st_geometry (it is already a class member).
In fact, I am able to
ST_POINT into an
ST_GEOMETRY superclass column in a table:
CREATE TABLE point_test (pt1 sde.st_geometry); INSERT INTO point_test VALUES ( sde.st_point (10.01, 20.03, 4326) );
But, as I said, I'm not able to use the previously mentioned view in ArcMap, due to the bug.
What I've tried:
I took a blind shot in the dark, and tried
CAST(SDE.ST_STARTPOINT(SHAPE) AS SDE.ST_GEOMETRY) AS GEOM
But it threw an error:
ORA-22907: invalid CAST to a type that is not a nested table or VARRAY (#22907)
How can I get ArcMap to recognize an ST_POINT subclass column? Do I need to convert the subclass to a superclass as a workaround to the bug?
Unfortunately, I don't have access to the Oracle bug documentation (Metalink Note:49375.1).