Inexperienced at using Oracle Spatial (SDO_GEOMETRY) and trying to use a procedure to load data latitude/longitude from a table dynamically using a LISTAGG function.
Keep getting an error "ORA-01722: invalid number" that I believe has to do with the LISTAGG (v_lat_long_list) variable. Using the debugger I can 'see' the same list of numbers that I was able to load without the variable below. Is there another way to load this SDO_GEOMETRY function dynamically?
CREATE OR REPLACE procedure test_insert
IS
v_lat_long_list VARCHAR2(32767);
BEGIN
select LISTAGG(GEOPOSITION_LONG|| ',' || GEOPOSITION_LAT || ',') WITHIN GROUP (ORDER BY GEOPOSITION_LONG)
into v_lat_long_list
from SECTOR_LAT_LONG
where sector_mod = '5710M1';
v_lat_long_list := rtrim(v_lat_long_list,','); -- to remove ending comma
INSERT INTO cola_markets_lat_long VALUES(
'5710M1',
SDO_GEOMETRY(
2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(v_lat_long_list)
)
);
commit;
END;
---------------
INSERT INTO cola_markets_lat_long VALUES(
'5710M1',
SDO_GEOMETRY(
2003,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1),
SDO_ORDINATE_ARRAY(
-120.02, 51.24,
-120.25, 51.19,
-120.25, 51.99,
-120.04, 51.93,
-120.95, 50.87,
-120.84, 50.92)
)
);
SDO_ORDINATE_ARRAY(v_lat_l
attempts to create a VARRAY of SDO_ORDINATE_ARRAY type using a set of numbers.
But, v_lat_long_list is a string of text
The fact that the string "looks" like numbers to a human is irrelevant, it's still just text.