5 Replies Latest reply: Apr 15, 2013 3:03 AM by 1002464 RSS

    sql code

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