How To: Bulk Insert St_geometry Attributes in Oracle

Summary

Instructions provided describe how to bulk insert rows into an Oracle table with an st_geometry attribute for optimal performance.

The example uses Oracle PL/SQL to define a collection, populate the collection with 100 st_geometry objects, and then write them to the database using 1 insert statement with the PL/SQL FORALL function.

Procedure

The procedure below defines a local varray, which is a collection of st_geometry objects. The example starts by obtaining the SRID for the event_points table from the sde.st_geometry_columns table. Next, it queries the event_points table to obtain an st_geometry object (just for purpose to demonstrate how to place 100 st_geometry objects into a collection, an actual example would likely be reading geometries from another source). The FOR i IN 1..100 LOOP shows how to set the local st_geometry geom variable and place the geometry into the gt collection. Once 100 geometries have been set in the collection, the example uses the FORALL function to perform the bulk insert into the event_points table.

Code:
DECLARE

 -- Local varray for collecting each geometry 
 TYPE geom_type IS VARRAY(100) OF sde.st_geometry;
 gt geom_type := geom_type();

 srid      INTEGER;
 curs1     INTEGER;
 stmt      VARCHAR2(512);
 nrows     NUMBER;
 row_oid   NUMBER;
 geom_txt  CLOB;
 geom      sde.st_geometry;

BEGIN

   SELECT srid INTO srid FROM sde.st_geometry_columns WHERE table_name = 'EVENT_POINTS';

   curs1 := dbms_sql.open_cursor;

   stmt := 'SELECT objectid, sde.st_astext(shape) FROM event_points WHERE objectid = 1';

   dbms_sql.parse(curs1,stmt,dbms_sql.native);

   dbms_sql.define_column(curs1, 1, row_oid);
   dbms_sql.define_column(curs1, 2, geom_txt);

   nrows := dbms_sql.execute(curs1);

   IF dbms_sql.fetch_rows(curs1) > 0 THEN
     dbms_sql.column_value(curs1,1,row_oid);
     dbms_sql.column_value(curs1,2,geom_txt);
   END IF;

   dbms_sql.close_cursor(curs1);

   FOR i IN 1..100 LOOP
    geom := sde.st_geometry(geom_txt,srid);
    gt.extend;
    gt(gt.count) := geom;
   END LOOP;

   FORALL i IN 1..100
     INSERT INTO event_points (objectid, shape) VALUES (r46.nextval, gt(i));

   gt.delete;
   
END;
/

Article ID: 000010886

Software:

Legacy Products

Discover more on this topic

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Bulk insert st_geometry attributes in Oracle

Summary

Procedure

Receive notifications and find solutions for new or common issues

Discover more on this topic