Replace value in SDO_ELEM_INFO_ARRAY varray

Question

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?

Answer 1

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