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?

score 1 · Accepted Answer · answered Jun 08 '22 at 15:15

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

answered Jun 08 '22 at 15:15

MT0

143,790
11
59
117

I want to learn more about this part: `v_geom.SDO_ELEM_INFO(3) := 3;`. Do you know if it's usually just called *"modifying a varray element"*, or does it have any other names/formal names? – User1974 Jun 11 '22 at 23:22

User1974 · Answer 2 · 2022-06-11T20:30:40.497

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))

This overwrites the entire `SDO_ELEM_INFO` varray with a new array rather than just modifying the 3rd element of the array (however, its much more complicated to just replace the 3rd element and keep everything else in SQL [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=78bdd45a09e1ab6808da9299dd1b7e45)). — MT0, Jun 08 '22 at 15:35

Replace value in SDO_ELEM_INFO_ARRAY varray

2 Answers2

Linked