I am having some issues with Oracle SQL as I am learning how to use it, but one thing seems not to be working and I cant see why:
I am trying to use the SDO_GEOMETRY objects to update a coordinate value in a table. I am trying to declare VARRAYs, filling these arrays with the coordinates and ELEM_INFO needed, and passing these arrays into the SDO_GEOMETRY obejct in the update part, like this:
CREATE OR REPLACE TYPE decimal_array is varray(728) OF NUMBER (10,9);
CREATE OR REPLACE TYPE int_array IS VARRAY(67) OF INTEGER;
> /
DECLARE
sdo_ordinate_arr decimal_array := decimal_array(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5);
info_arr int_array := int_array(1,1003,1, 19,2003,1);
begin
update schema.table set geo_coord = (SDO_GEOMETRY(2003, 25832, NULL,
SDO_ELEM_INFO_ARRAY(info_arr), SDO_ORDINATE_ARRAY(sdo_ordinate_arr))) where id = 123456;
END;
> /
I tried the SQL above, and expected it to work, but it did not. There are no errors being raised, but the value in the table is not being updated.
If i do it like this, without the arrays but using the same values, it works:
update schema.table set geo_coord_ = (SDO_GEOMETRY(2003, 25832, NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1), SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5))) where id = 123456;
As I understand from the documentation, SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY are both declared as arrays. Why doesnt it work if passed an array as value?
Got it, you need to declare your arrays as SDO_ELEM_INFO_ARRAY and SDO_ORDINATE_ARRAY, not as VARRAYs:
sdo_ordinate_arr SDO_ORDINATE_ARRAY:= SDO_ORDINATE_ARRAY(2,4, 4,3, 10,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,10, 10,10, 10,5, 7,5)
info_arr SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1)
Oracle is confusing.