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)
)
    );
talahiAsked:
Who is Participating?
 
sdstuberCommented:
or, if you want, you can do it all in a single sql statement


INSERT INTO cola_markets_lat_long
      SELECT sector_mod,
             sdo_geometry(
                 2003,
                 NULL,
                 NULL,
                 sdo_elem_info_array(1, 1003, 1),
                 CAST(
                     COLLECT(DECODE(n, 1, geoposition_long, geoposition_lat) ORDER BY
                                                                                 geoposition_long,
                                                                                 geoposition_lat,
                                                                                 n) AS sdo_ordinate_array
                 )
             )
        FROM (SELECT n, x.*
                FROM sector_lat_long x,
                     (SELECT 1 n FROM DUAL
                      UNION ALL
                      SELECT 2 FROM DUAL)
               WHERE sector_mod = '5710M1')
    GROUP BY sector_mod;
0
 
sdstuberCommented:
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.
0
 
sdstuberCommented:
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;
0
 
talahiAuthor Commented:
Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.