Treat Oracle subtype as supertype

Question

I have a query that uses Oracle's MDSYS.ST_GEOMETRY type (link):

select
    mdsys.st_point(1, 2, 26917)
from 
    dual

Output:
[MDSYS.ST_POINT]

The query outputs the ST_POINT subtype.

I want to convert the ST_POINT subtype to the ST_GEOMETRY supertype:

select
    treat(
        mdsys.st_point(1, 2, 26917)
    as st_geometry)
    .st_geometrytype() --optional; helps when running the query in DB<>FIDDLE or Oracle Live SQL, since those DBs don't output spatial types correctly.   
from 
    dual

Output:
ST_POINT

I would have thought that I could use the TREAT() function to make the conversion.

TREAT()

You can use the TREAT function to change the declared type of an expression.

But as you can see in the query above, even though I used the TREAT() function, the output is still the ST_POINT subtype, not the ST_GEOMETRY supertype.

And for what it's worth, the same thing happens with a User-defined Type.

---

How can I convert an Oracle subtype to its supertype?

Related: 2.3 Inheritance in SQL Object Types

Answer 1

The st_geometry uses sdo_geometry as its storage. So you would assume the TREAT would happily return the st_geometry as no storage change is needed.

select mdsys.st_point(1,2,26917).geom.get_wkt() as geom from dual;

Conversion can be done directly,:

select Mdsys.st_geometry(mdsys.st_point(1, 2, 26917).geom) as geom from dual

Answer 2

To build on @Simon's answer, here are the results from Simon's queries:

Query #1:

 select mdsys.st_point(1,2,26917).geom as geom from dual;
 --I removed .get_wkt().
 
 Result:
 [MDSYS.SDO_GEOMETRY]

Query #2:

 select Mdsys.st_geometry(mdsys.st_point(1, 2, 26917).geom) as geom from dual
 
 Result:
 [MDSYS.ST_GEOMETRY]

So I think that means both queries worked. They converted the ST_POINT subtype to the ST_GEOMETRY supertype.

---

I used CTRL+F5 to run the queries in SQL Developer. If I'd used plain F5, I would have gotten different results, because F5/SQLPLUS automatically converts the result to a textual representation of the geometry. So the result for the queries would have been POINT (1.0 2.0), which is misleading. The result is really [MDSYS.ST_GEOMETRY].

---

Related: Unsupported datatype in Live SQL: st_geometry(st_point(1, 2, 26917).geom)