enter image description here

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 column. st_point is a subclass of st_geometry; st_geometry is the superclass.


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 insert an ST_POINT into an ST_GEOMETRY superclass column in a table:

CREATE TABLE point_test (pt1 sde.st_geometry);
 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:


But it threw an error:

ORA-22907: invalid CAST to a type that is not a nested table or VARRAY (#22907)

The Question:

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).

  • Comments are not for extended discussion; this conversation has been moved to chat. – Mapperz Nov 22 '16 at 17:28
  • In fact SDE have it own geometry type. In SQL Server it reconnize has custom type. I don't think you can Cast this because this is two differents schema. so you need pass data as text format. Look at link you can create function to pass data sdo to sde and reverse. – GeoStoneMarten Mar 7 '17 at 10:25

I don't have an SDE to play with. I think you would have to do something like this:

With point As (
 select sde.st_point (10.01, 20.03, 4326) as pt from dual
select sde.st_geometry(sde.ST_X(p.pt), sde.ST_Y(p.pt), sde.ST_Z(p.pt), sde.ST_M(p.pt), sde.ST_SRID(p.pt)) as ptGeom 
 from point p;

Or you could use WKB.

My packages and objects have implementations of ST_StartPoint that return SDO_GEOMETRY objects. You could create a function that does the same thing but return a point geometry:

create function ST_Point2Geom(p_pt in sde.st_point)
returns sde.st_geometry deterministic
  return case when p_pt is null 
              then null 
              when sde.ST_GeometryType(p_pt) <> 'ST_POINT'
              then p_pt 
              else sde.st_geometry(sde.ST_X(p_pt), sde.ST_Y(p_pt),
                                   sde.ST_Z(p_pt), sde.ST_M(p_pt),
end st_point2geom;
show errors

select st_point2Geom(sde.st_startPoint(myGeom)) from dual;
  • Exacty that i speak about in comment! I think you proposed a good solution to workaround. – GeoStoneMarten Mar 9 '17 at 8:46
  • other sotultion can be use TO_CHAR and remade point in native ST_POINT – GeoStoneMarten Mar 9 '17 at 8:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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