The Oracle IS OF TYPE comparison operator (IS NULL) allows for the type of an object to be examined. The following example demonstrates its use:
Select Case When Then End MDSYS.ST_Point.FROM_WKT('POINT(6012578.005 2116495.361)',
2872) is of type (MDSYS.ST_Point)
then 'POINT'
Else 'GEOMETRY'
end as ofTypePoint,
case when MDSYS.ST_Point.FROM_WKT('POINT(6012578.005 2116495.361)',
2872) is of type (MDSYS.ST_Geometry)
then 'GEOM'
Else 'Something Else'
end as ofTypeGeom,
case when MDSYS.ST_Point.FROM_WKT('POINT(6012578.005 2116495.361)',
2872) is of type (ONLY MDSYS.ST_Geometry)
then 'GEOM'
Else 'Something Else'
end as ofTypeOnlyGeom
From dual;
-- Results
--
OFTYPEPOINT OFTYPEGEOM OFTYPEONLYGEOM
----------- -------------- --------------
POINT GEOM Something ElseThe first two results are perfectly correct as an ST_POINT type is both an ST_POINT and an ST_GEOMETRY. The use of the ONLY prefix reports that ST_POINT is not just an ST_GEOMETRY type if IS OF TYPE reports that the result of ST_POINT.FROM_WKT is an ST_POINT.