2 Replies Latest reply: Sep 26, 2012 4:54 AM by Stefan Jager RSS

    How to pass a variable to MDSYS.SDO_ORDINATE_ARRAY

    961754
      I am attempting to pass a variable to MDSYS.SDO_ORDINATE_ARRAY can hard code the value but cannot pass in the variable. UDT does not accept char strings.
      value for insert MDSYS.SDO_GEOMETRY(2002, 8307, null,MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),MDSYS.SDO_ORDINATE_ARRAY( ))

      if I hard code
      MDSYS.SDO_GEOMETRY(2002, 8307, null,MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1),MDSYS.SDO_ORDINATE_ARRAY(0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4 ))
      works

      input variable
      ,p_linear_ring IN VARCHAR2
      is passed from another application so p_linear_ring is not in a table using a select statment is not possible

      will look like
      p_linear_ring := '0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4 '

      Loading many different combination of p_linear_ring

      thanks
      Paul
        • 1. Re: How to pass a variable to MDSYS.SDO_ORDINATE_ARRAY
          Simon Greener
          You could try using the WKT SDO_GEOMETRY constructor as follows:
          select SDO_GEOMETRY('LINESTRING(' || '0 0,10 0,10 10,0 10,0 0,4 4,6 4,6 6,4 6,4 4' || ')',8307) as lGeom 
            from dual;
          -- Results
          --
          LINESTRING
          ----------------------------------------------------------------------------------------------------------------------
          SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0,0,10,0,10,10,0,10,0,0,4,4,6,4,6,6,4,6,4,4))
          Just make sure that the ordinate string is formatted the way that WKT wants it.

          But if that is no good, here is an all-singing-and-dancing bit of funkiness. (You will need a StringTokenizer to make it work - see mine on my website or anyone else's out there in WebLand.)
          SELECT mdsys.sdo_geometry(2002,8307,NULL,
                                    mdsys.sdo_elem_info_array(1,2,1),
                                    CAST(MULTISET(SELECT t.token
                                                    FROM table(codesys.tokenizer('0,0, 10,0, 10,10, 0,10, 0,0, 4,4, 6,4, 6,6, 4,6, 4,4 ',', ')) t
                                                   WHERE t.token is not null 
                                                   ORDER BY id) 
                                        AS mdsys.sdo_ordinate_array)) AS LineString
            FROM dual;
          -- Results
          --
          LINESTRING
          -----------------------------------------------------------------------------------------------------------------------
          SDO_GEOMETRY(2002,8307,NULL,SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(0,0,10,0,10,10,0,10,0,0,4,4,6,4,6,6,4,6,4,4))
          Don't forget to award points: either right or helpful.

          regards
          Simon
          • 2. Re: How to pass a variable to MDSYS.SDO_ORDINATE_ARRAY
            Stefan Jager
            That's because the ORDINATE_ARRAY is an array, not a string. Possible solutions are to use the WKT as SImon says, or create an insert or update statement using the string and execute immediate the statement. Or you can parse the string into an ORDINATE ARRAY.

            If you show some code, I might be able to give you bit more detailed pointers.

            Cheers,
            Stefan