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