oracle-databaseinheritancetype-conversionoracle18coracle-spatial

Treat Oracle subtype as supertype


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


Solution

  • 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