English

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;
/