This question pertains to Oracle 18c's MDSYS.ST_GEOMETRY datatype. Which is not to be confused with Esri's SDE.ST_GEOMETRY datatype).
The following query produces an MDSYS.ST_LINESTRING subtype value:
with data as (
select st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)
select
shape
from
data
Result:
[MDSYS.ST_LINESTRING]
In a query, I want to convert the MDSYS.ST_LINESTRING subtype value to the MDSYS.ST_GEOMETRY supertype.
Reason: My GIS software can only handle MDSYS.ST_GEOMETRY supertype values (not MDSYS.ST_LINESTRING subtype values). I don't have control over this limitation.
I tried using the TREAT() function to make the covnersion, but it didn't seem to have an effect. The result is still the MDSYS.ST_LINESTRING subtype:
with data as (
select st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)
select
treat(shape as mdsys.st_geometry)
from
data
The result is still:
[MDSYS.ST_LINESTRING]
How can I convert the MDSYS.ST_LINSTRING subtype value to the MDSYS.ST_GEOMETRY supertype?
@SimonGreener pointed out that this works:
with data as (
select st_linestring.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)
select
treat(st_geometry(a.shape.geom) as mdsys.st_geometry) as shape
from
data a
SHAPE
--------------
[MDSYS.ST_GEOMETRY]