This discussion is archived
5 Replies Latest reply: Apr 15, 2013 1:03 AM by 1002464 RSS

sql code

1002464 Newbie
Currently Being Moderated
HI im trying to update large of coordinates into a fow.

following this code:

update TEST set geom = mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,1),mdsys.sdo_ordinate_array(1481106.940,5323026.721,1481109.902,5323153.359,1481110.644,5323185.113,1481083.576,5323185.746,1481078.801,5323230.338,1481042.181,5323225.028,1481040.226,5323215.992,1481039.956,5323196.432,1481041.068,5323179.479,1481045.329,5323160.838,1481049.260,5323146.593,1481040.709,5323145.639,1481052.172,5323114.988,1481069.593,5323080.307,1481103.418,5323026.804,1481106.940,5323026.721))where FID = 2979;

i got error: ORA-0939: too many arguments for function.

and i searched over google and found this code:

CREATE OR REPLACE PROCEDURE p_test is
DECLARE gm mdsys.sdo_geometry;
BEGIN
gm := mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,1),mdsys.sdo_ordinate_array(1461977.319,6555885.633,1461979.695,6556010.733,1461980.291,6556042.101,1461953.551,6556042.609,1461948.641,6556086.634,1461912.493,6556081.23,1461910.601,6556072.297,1461910.419,6556052.975,1461911.591,6556036.234,1461915.881,6556017.84,1461919.826,6556003.787,1461911.383,6556002.807,1461922.839,6555972.581,1461940.197,6555938.401,1461973.84,6555885.7,1461977.319,6555885.633));
EXECUTE IMMEDIATE
'update TEST set geom=:gm where FID =2979'
USING gm;
End;
/

But when i run this the fow which have FID 2979 is not updated.

Can anyone see whats is wrong?
  • 1. Re: sql code
    John O'Toole Journeyer
    Currently Being Moderated
    Hi user 999461,

    Stick to the sql in this case. You don't need pl/sql to run this simple update. Check your sql again. See example below:
    jot_test@OSI> create table test_table (
      2     fid number,
      3     geom sdo_geometry);
    
    Table created.
    
    jot_test@OSI>
    jot_test@OSI> insert into test_table (fid) values (2979);
    
    1 row created.
    
    jot_test@OSI>
    jot_test@OSI> update test_table set geom = mdsys.sdo_geometry(2003,NULL,NULL,mdsys.sdo_elem_info_array(1,1003,1),
      2  mdsys.sdo_ordinate_array(1481106.940,5323026.721,1481109.902,5323153.359,1481110.644,5323185.113,1481083.576,5323185.746,
      3  1481078.801,5323230.338,1481042.181,5323225.028,1481040.226,5323215.992,1481039.956,5323196.432,1481041.068,5323179.479,
      4  1481045.329,5323160.838,1481049.260,5323146.593,1481040.709,5323145.639,1481052.172,5323114.988,1481069.593,5323080.307,
      5  1481103.418,5323026.804,1481106.940,5323026.721))
      6  where fid = 2979;
    
    1 row updated.
    John
  • 2. Re: sql code
    _jum Journeyer
    Currently Being Moderated
    Your UPDATE works fine with me:
    UPDATE test
       SET geom =
             mdsys.sdo_geometry (2003,
                                 NULL,
                                 NULL,
                                 mdsys.sdo_elem_info_array (1, 1003, 1),
                                 mdsys.sdo_ordinate_array(1481106.940,5323026.721,1481109.902,5323153.359,1481110.644,5323185.113,1481083.576,5323185.746,1481078.801,5323230.338,
                                                           1481042.181,5323225.028,1481040.226,5323215.992,1481039.956,5323196.432,1481041.068,5323179.479,1481045.329,5323160.838,
                                                         1481049.260,5323146.593,1481040.709,5323145.639,1481052.172,5323114.988,1481069.593,5323080.307,1481103.418,5323026.804,1481106.940,5323026.721))
     WHERE fid = 2979;
  • 3. Re: sql code
    1002464 Newbie
    Currently Being Moderated
    yes for FID 2979 I can update this simple SQL update, but my problem store at FID 3204 which stored 4012 coordinates.

    I cant post those coordinates here, coz I only can write 30000 characters in this post.
  • 4. Re: sql code
    _jum Journeyer
    Currently Being Moderated
    There is normally no problem to UPDATE a geometry with more than 5000 coordinates (here for simplicity a MULTIPOINT]:
    INSERT INTO test_table
    WITH coords AS
      (SELECT ROUND(dbms_random.value(0,100000),3)x  FROM dual)
    SELECT 1234 fid, mdsys.sdo_geometry(2005,NULL,NULL,
             mdsys.sdo_elem_info_array(1,1, 5000),
             CAST(MULTISET(SELECT x FROM coords CONNECT BY level<=10000) AS mdsys.sdo_ordinate_array)) 
     FROM dual
     GROUP by 1;
    
    SELECT SDO_GEOM.VALIDATE_GEOMETRY(geom, 0.0001) val, 
           SDO_UTIL.GETNUMVERTICES(geom) gn 
      FROM test_table;  
    
    val     gn
    ----------------
    TRUE     5000
      
    UPDATE test_table
       SET geom =
        mdsys.sdo_geometry (2003,
                                 NULL,
                                 NULL,
                                 mdsys.sdo_elem_info_array (1, 1003, 1),
                                 mdsys.sdo_ordinate_array(1481106.940,5323026.721,1481109.902,5323153.359,1481110.644,5323185.113,1481083.576,5323185.746,1481078.801,5323230.338,
                                                          1481042.181,5323225.028,1481040.226,5323215.992,1481039.956,5323196.432,1481041.068,5323179.479,1481045.329,5323160.838,
                                                          1481049.260,5323146.593,1481040.709,5323145.639,1481052.172,5323114.988,1481069.593,5323080.307,1481103.418,5323026.804,1481106.940,5323026.721))
     WHERE fid = 1234; 
    
    SELECT SDO_GEOM.VALIDATE_GEOMETRY(geom, 0.0001) val, 
           SDO_UTIL.GETNUMVERTICES(geom) gn 
      FROM test_table;  
    
    val      gn
    ----------------
    TRUE      16
    How do you build up your UPDATE?
  • 5. Re: sql code
    1002464 Newbie
    Currently Being Moderated
    in here can u see the full code for update:

    http://antonios-code-sample.googlecode.com/svn/sql

    in sqlpus i typed:

    SELECT SDO_GEOM.VALIDATE_GEOMETRY(geom, 0.0001) val, SDO_UTIL.GETNUMVERTICES(geom) gn FROM test where FID = 3204

    VAL GN
    ---- ----
    TRUE 2026
    1 rows selected.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points