Replace value in SDO_ELEM_INFO_ARRAY varray-CodePudding

I have an existing SDO_GEOMETRY in Oracle 18c:

sdo_geometry(2003, null, null, sdo_elem_info_array(1, 1003, 1),
    sdo_ordinate_array(665287.423,4857578.086,   676832.320,4878119.585))

In the sdo_elem_info_array varray, I want to replace the final 1 with a 3:

Before: (1, 1003, 1)
After: (1, 1003, 3)

How can I replace that value in the sdo_elem_info_array varray?

CodePudding user response：

You can create a user-defined function and edit it using PL/SQL:

WITH FUNCTION edit_info(i_geom SDO_GEOMETRY) RETURN SDO_GEOMETRY
IS
  v_geom SDO_GEOMETRY := i_geom;
BEGIN
  v_geom.SDO_ELEM_INFO(3) := 3;
  RETURN v_geom;
END;
SELECT edit_info(
         sdo_geometry(
           2003,
           null,
           null,
           sdo_elem_info_array(1, 1003, 1),
           sdo_ordinate_array(665287.423, 4857578.086, 676832.320, 4878119.585)
         )
       )
FROM   DUAL;

db<>fiddle here

CodePudding user response：

Similar to @MTO's solution, I suppose I could do it in a query too (without the need for a custom function):

with cte as (
select sdo_geometry(2003, 26917, null, sdo_elem_info_array(1, 1003, 1), sdo_ordinate_array(665287.423, 4857578.086, 676832.320, 4878119.585)) shape
from   dual)

select sdo_geometry(a.shape.sdo_gtype,
                    a.shape.sdo_srid,
                    a.shape.sdo_point,
                    sdo_elem_info_array(1, 1003, 3),
                    a.shape.sdo_ordinates) as shape
from cte a;

Result:
MDSYS.SDO_GEOMETRY(2003, 26917, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 3), MDSYS.SDO_ORDINATE_ARRAY(665287.423, 4857578.086, 676832.32, 4878119.585))