Modify object attribute/property — without creating custom function-CodePudding

I have an Oracle 18c SDO_GEOMETRY object that has attributes (aka properties):

with cte as (
select 
    sdo_util.from_wktgeometry('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 -10000))') shape
from dual)

select 
    a.shape.sdo_gtype as old_gtype,
    a.shape.sdo_gtype   300 as new_gtype,
    a.shape
from 
    cte a


OLD_GTYPE  NEW_GTYPE    SHAPE 
---------  ---------    -----
     3006       3306    SDO_GEOMETRY(3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 10, 30, 0, 33.54, 50, 10, 33.54, 60, 10, -10000))

I want to modify the GTYPE attribute of the SDO_GEOMETRY object:

Old GTYPE: 3006
New GTYPE: 3306

It's possible to modify the GTYPE attribute using a custom function (or an inline function):

See @AlbertGodfrind's answer in Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL

However, as an experiment, I want to modify the GTYPE attribute right in the SELECT clause in a query -- without using a custom function.

For example, I wonder if there might be OOTB functionality like this:

modify_object_property(object, property_name, new_val) returns sdo_geometry

Is there a way to modify an SDO_GEOMETRY GTYPE attribute/property — without creating a custom function?

CodePudding user response：

You can modify the contents of an object in SQL. You just need to make sure you use an alias on the table. For example:

update my_table t
set t.geom.sdo_gtype = t.geom.sdo_gtype   300
where ... ;

But that is not what you are looking for. Modifying an object that way in the select list is not possible. Hence the approach via a custom function.

CodePudding user response：

You can use the sdo_geometry constructor as follows



    with cte as (
    select 
        sdo_util.from_wktgeometry('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 -10000))') shape
    from dual)
    select sdo_geometry(a.shape.sdo_gtype   300,
                        a.shape.sdo_srid,
                        a.shape.sdo_point,
                        a.shape.sdo_elem_info,
                        a.shape.sdo_ordinates) as shape
      from cte a;