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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
talahiAuthor Commented:
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.