Transactional Insert Operations Using SQL

4.2 Transactional Insert Operations Using SQL

Oracle Spatial uses standard Oracle tables that can be accessed or loaded with standard SQL syntax. This topic contains examples of transactional insertions into columns of type SDO_GEOMETRY. This process is typically used to add relatively small amounts of data into the database.

The INSERT statement in Oracle SQL has a limit of 999 arguments. Therefore, you cannot create a variable-length array of more than 999 elements using the SDO_GEOMETRY constructor inside a transactional INSERT statement; however, you can insert a geometry using a host variable, and the host variable can be built using the SDO_GEOMETRY constructor with more than 999 values in the SDO_ORDINATE_ARRAY specification. (The host variable is an OCI, PL/SQL, or Java program variable.)

To perform transactional insertions of geometries, you can create a procedure to insert a geometry, and then invoke that procedure on each geometry to be inserted. Example 4-4 creates a procedure to perform the insert operation.

Example 4-4 Procedure to Perform a Transactional Insert Operation

CREATE OR REPLACE PROCEDURE
        INSERT_GEOM(GEOM SDO_GEOMETRY)
IS

BEGIN
  INSERT INTO TEST_1 VALUES (GEOM);
  COMMIT;
END;
/

Using the procedure created in Example 4-4, you can insert data by using a PL/SQL block, such as the one in Example 4-5, which loads a geometry into the variable named geom and then invokes the INSERT_GEOM procedure to insert that geometry.

Example 4-5 PL/SQL Block Invoking a Procedure to Insert a Geometry

DECLARE
geom SDO_geometry :=
  SDO_geometry (2003, null, null,
          SDO_elem_info_array (1,1003,3),
          SDO_ordinate_array (-109,37,-102,40));
BEGIN
  INSERT_GEOM(geom);
  COMMIT;
END;
/

For additional examples with various geometry types, see the following: