Error if coordinates in SDO_ORDINATE_ARRAY has decimals

Question

I´m trying to insert a geometry into a table using the Oracle SDO_GEOMETRY objects. If I use the following coordinates without decimals, it works without issue:


DECLARE 
ordinate_arr SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY(2,4, 4,3, 9,3, 13,5, 13,9, 11,13, 5,13, 2,11, 2,4, 7,5, 7,9, 9,9, 9,5, 7,5); 
info_arr SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1); 
begin 
update schema.table set geo_coord = (SDO_GEOMETRY(2003, 25832, NULL, 
info_arr, ordinate_arr)) where id = 332564; 
END;

However, if I use coordinates with decimals, it throws an ORA-06502 Error. Here is the code with decimals:

DECLARE 
ordinate_arr SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY(2.5, 4.5, 4.5, 3.5, 9.5, 3.5, 13.5, 5.5, 13.5, 9.5, 11.5, 13.5, 5.5, 13.5, 2.5, 11.5, 2.5, 4.5, 7.5, 5.5, 7.5, 9.5, 9.5, 9.5, 9.5, 5.5, 7.5, 5.5); 
info_arr SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1); 
begin 
update schema.table set geo_coord = (SDO_GEOMETRY(2003, 25832, NULL, 
info_arr, ordinate_arr)) where id = 123456; 
END;

and here is the error that is raised:

DECLARE 
ordinate_arr SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY(2.5, 4.5, 4.5, 3.5, 9.5, 3.5, 13.5, 5.5, 13.5, 9.5, 11.5, 13.5, 5.5, 13.5, 2.5, 11.5, 2.5, 4.5, 7.5, 5.5, 7.5, 9.5, 9.5, 9.5, 9.5, 5.5, 7.5, 5.5); 
info_arr SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY(1,1003,1, 19,2003,1); 
begin 
update schema.table set geo_coord = (SDO_GEOMETRY(2003, 25832, NULL, 
info_arr, ordinate_arr)) where id = 123456; 
END;
Fehlerbericht -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 2
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

If I, however, use a normal update query like this, including decimals, it works without errors:

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.5, 4.5, 4.5, 3.5, 9.5, 3.5, 13.5, 5.5, 13.5, 9.5, 11.5, 13.5, 5.5, 13.5, 2.5, 11.5, 2.5, 4.5, 7.5, 5.5, 7.5, 9.5, 9.5, 9.5, 9.5, 5.5, 7.5, 5.5))) 
where id = 123456;

Any idea why is this happening?

Error if coordinates in SDO_ORDINATE_ARRAY has decimals

0 Answers0