Convert UDT subtype to supertype

Question

I use a user-defined type (UDT) in Oracle 18c called ST_GEOMETRY:

• The ST_Geometry storage type
• ST_Geometry in Oracle
• SQL functions used with ST_Geometry

I have a query that produces a ST_GEOMETRY supertype:

select
    sde.st_geometry('LINESTRING(1 2,3 4,5 6)', 26917) as geom
from
    dual
    
[SDE.ST_GEOMETRY]

I want to select a specific vertex of that geometry -- the startpoint of that ST_GEOMETRY line:

select
    sde.st_startpoint(sde.st_geometry('LINESTRING(1 2,3 4,5 6)', 26917)) as startpoint
from
    dual
    
[SDE.ST_POINT]

Notice that the output is the ST_POINT subtype, not the ST_GEOMETRY supertype.

The startpoint query runs without errors in an SQL client. However, I want to use the query in GIS software that has a limitation where it can't use the ST_POINT subtype. It can only use the ST_GEOMETRY supertype.

The software vendor blames Oracle for this issue: Bug: Unable to define a query layer in ArcGIS where the data source uses an st_geometry subtype in Oracle. The software vendor suggests the following workaround (although they don't say how to do it):

Convert the geometry attribute field from the subtype (for example, st_point) to the supertype st_geometry.

So, I want to use Oracle's TREAT() function to convert from the ST_POINT subtype to the ST_GEOMETRY supertype:

select
    treat(sde.st_startpoint(sde.st_geometry('LINESTRING(1 2,3 4,5 6)', 26917)) as sde.st_geometry) startpoint_geom
from
    dual

[SDE.ST_POINT]

That query runs without errors in an SQL client. But it doesn't seem to be converting to the ST_GEOMETRY supertype correctly. The output is still the ST_POINT subtype, which isn't what I want. (It’s still causing problems in the GIS software.)

---

How can I use the TREAT() function to convert a UDT subtype to the supertype?

I'm aware that there are other workarounds like converting from subtype to text to supertype. But that is ugly and slow. I would prefer to use the TREAT() function to do the conversion, since I think that would be cleaner/faster.