Convert ST_POINT subtype to ST_GEOMETRY supertype?

Question

Oracle 18c; ArcGIS 10.7.1

I have a query that produces an ST_POINT column:

--Mimics the SDO_GEOMETRY get_vertices function 
--https://docs.oracle.com/database/121/SPATL/sdo_util-getvertices.htm

create or replace view road_vertices as (
select
    road_id,
    sde.st_pointn(shape,n.num) vertex_st_point
from
    roads s
cross join
    numbers n
where
    n.num <= sde.st_numpoints(shape)
)

That query works fine in when I run it in an SQL client.

---

Problem:

I run into various issues when I try to use the query in ArcGIS Desktop. For example, when I try to use the query as a layer in ArcMap, I get an error from the ST_POINT shape column:

Warning

Could not add the specified data object to the map.
Wrong column type[Invalid Geometry Storage Type]

Note: ESRI has documented the problem here:

Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle

But that article doesn't tell us what the actual fix is.

---

Ugly Fix:

If I convert the ST_POINT subtype to the ST_GEOMETRY supertype, then I can add the query to ArcMap without issue.

sde.st_geometry(sde.st_astext(   sde.st_pointn(shape,n.num)   ,26917) as vertex_st_geom

The only way I've found to do that is to convert the the ST_POINT to WKT and then to ST_GEOMETRY.

However, in my opinion, it seems pretty backwards to use sde.st_astext and sde.st_geometry to perform the conversion (and adds a significant cost).

---

Question:

Is there a cleaner way to convert the ST_POINT subtype to the ST_GEOMETRY supertype?

Answer 1

I can use Oracle's TREAT() function to convert the ST_POINT subtype to the ST_GEOMETRY supertype.

treat(sde.st_pointn(shape,n.num) as sde.st_geometry) as vertex_st_geometry 

Once I do that, I can add the query to ArcMap without issue.