asked on

Oracle Spatial (SDO_GEOMETRY) using procedure to load data dynamically with LISTAGG function.

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

Sean Stuber

this line...

SDO_ORDINATE_ARRAY(v_lat_long_list)

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.

Sean Stuber

Instead of aggregating into a text string, agrregate into the SDO_ORDINATE_ARRAY

CREATE OR REPLACE PROCEDURE test_insert
IS
v_lat_long_list sdo_ordinate_array;
BEGIN
SELECT CAST(
COLLECT(DECODE(n, 1, geoposition_long, geoposition_lat) ORDER BY
geoposition_long,
geoposition_lat,
n) AS sdo_ordinate_array
)
INTO v_lat_long_list
FROM (SELECT n, x.*
FROM sector_lat_long x,
(SELECT 1 n FROM DUAL
UNION ALL
SELECT 2 FROM DUAL)
WHERE sector_mod = '5710M1');

INSERT INTO cola_markets_lat_long
VALUES (
'5710M1',
sdo_geometry(
2003,
NULL,
NULL,
sdo_elem_info_array(1, 1003, 1),
v_lat_long_list
)
);

COMMIT;
END;

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

talahi

ASKER

Thanks again.