how to save array which contains lat/lng to sdo_geometry object in oracle stored procedure

Question

I make stored procedure inside package. I want to store an array of point consists of lat/lng in sdo_geometry object.

First declare type inside package

TYPE p_int_type IS TABLE OF number;

then define procedure definition.

  PROCEDURE PRC_POLYGON_ADD_TESTING(
  pPointArray IN p_int_type,
  pCODE OUT VARCHAR2,
  pDesc OUT VARCHAR2,
  pMSG OUT VARCHAR2 );

this is my procedure body

 PROCEDURE PRC_POLYGON_ADD_TESTING
    (
      pPointArray IN p_int_type,
      pCODE OUT VARCHAR2,
      pDesc OUT VARCHAR2,
      pMSG OUT VARCHAR2
    )
  AS
  BEGIN
      INSERT
    INTO CIRCLE_LOCATION
      (
        polygon
      )
      VALUES
      (
        sdo_geometry ( 2003      -- 2D Polygon
        , 4326                   -- WGS84, the typical GPS coordinate system
        , NULL                   -- sdo_point_type, should be NULL if sdo_ordinate_array specified
        , sdo_elem_info_array( 1 -- First ordinate position within ordinate array
        , 1003                   -- Exterior polygon
        , 1                      -- All polygon points are specified in the ordinate array
        ) , sdo_ordinate_array(pPointArray))
      );
  END PRC_POLYGON_ADD_TESTING;

But i got error that local collection type is not allowed here. Please guide me how i save full array consisting of points lat/lng in sdo_geometry object. Points are dynamic that's why I want to use array.

Answer 1

SDO_ORDINATE_ARRAY and P_INT_TYPE are not the same data type and you need to convert the values from one type to the other.

Create the package:

CREATE PACKAGE package_name IS
  TYPE p_int_type IS TABLE OF number;

  PROCEDURE PRC_POLYGON_ADD_TESTING(
    pPointArray IN p_int_type,
    pCODE OUT VARCHAR2,
    pDesc OUT VARCHAR2,
    pMSG OUT VARCHAR2
  );
END;
/

Then the body, and you need to convert from p_int_type to SDO_ORDINATE_ARRAY:

CREATE PACKAGE BODY package_name IS
  PROCEDURE PRC_POLYGON_ADD_TESTING(
    pPointArray IN p_int_type,
    pCODE OUT VARCHAR2,
    pDesc OUT VARCHAR2,
    pMSG OUT VARCHAR2
  )
  AS
    coords SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY();
  BEGIN
    coords.EXTEND( pPointArray.COUNT );
    FOR i IN 1 .. pPointArray.COUNT LOOP
      coords(i) := pPointArray(i);
    END LOOP;

    INSERT INTO CIRCLE_LOCATION
    (
      polygon
    ) VALUES (
      sdo_geometry(
          2003                   -- 2D Polygon
        , 4326                   -- WGS84, the typical GPS coordinate system
        , NULL                   -- sdo_point_type, should be NULL if sdo_ordinate_array specified
        , sdo_elem_info_array(
            1                    -- First ordinate position within ordinate array
          , 1003                 -- Exterior polygon
          , 1                    -- All polygon points are specified in the ordinate array
        )
      , coords
      )
    );
  END PRC_POLYGON_ADD_TESTING;
END;
/

Then you can call the package to insert the values:

DECLARE
  pCODE VARCHAR2(20);
  pDesc VARCHAR2(20);
  pMsg  VARCHAR2(20);
BEGIN
  PACKAGE_NAME.PRC_POLYGON_ADD_TESTING(
    PACKAGE_NAME.P_INT_TYPE( 0,0,0,10,10,10,10,0,0,0 ),
    pCode,
    pDesc,
    pMsg
  );
END;
/

db<>fiddle here

---

However, you may be better to declare the procedure as taking a SDO_ORDINATE_ARRAY rather than declaring your own collection data type and then you don't need to convert between types.