Avatar of talahi
talahi
 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)
)
    );
Oracle Database

Avatar of undefined
Last Comment
talahi

8/22/2022 - Mon
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
talahi

ASKER
Thanks again.
Your help has saved me hundreds of hours of internet surfing.
fblack61