I have a SDE.ST_GEOMETRY line feature class. I've created a view on it that returns an st_point column.


There is a known Oracle bug that is preventing me from using my view : 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 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 the subclass to the superclass.


How can I get ArcMap to recognize an ST_POINT subclass column?

  • 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
  • @GeoStoneMarten Oracle's TREAT() function solves this issue. – Wilson Jan 31 at 13:24

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
  • Oracle's TREAT() function solves this issue. – Wilson Jan 31 at 13:32

Oracle's TREAT() function solves this issue:

    treat(  sde.st_pointonsurface(sde.st_buffer(shape,0.05)  ) as sde.st_geometry) as midpoint 

This blurb actually describes the opposite of what I'm looking for (supertype to subtype, not subtype to supertype). But I still found it helpful:

The Oracle TREAT function allows you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one.

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.