Bulk Loading

Bulk loading can import large amounts of data into an Oracle AI Database.

Bulk loading is accomplished with the SQLLoader utility. (For information about SQLLoader, see Oracle AI Database Utilities.)

Bulk Loading SDO_GEOMETRY Objects

Example: Control File for a Bulk Load of Cola Market Geometries is the SQLLoader control file for loading four geometries. When this control file is used with SQLLoader, it loads the same cola market geometries that are inserted using SQL statements in Simple Example: Inserting_ Indexing_ and Querying Spatial Data.

Example: Control File for a Bulk Load of Cola Market Geometries

LOAD DATA
INFILE *
TRUNCATE
CONTINUEIF NEXT(1:1) = '#'
INTO TABLE COLA_MARKETS
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
mkt_id INTEGER EXTERNAL,
name CHAR,
shape COLUMN OBJECT
(
SDO_GTYPE INTEGER EXTERNAL,
SDO_ELEM_INFO VARRAY TERMINATED BY '|/'
(elements FLOAT EXTERNAL),
SDO_ORDINATES VARRAY TERMINATED BY '|/'
(ordinates FLOAT EXTERNAL)
)
)
begindata
 1|cola_a|
#2003|1|1003|3|/
#1|1|5|7|/
 2|cola_b|
#2003|1|1003|1|/
#5|1|8|1|8|6|5|7|5|1|/
 3|cola_c|
#2003|1|1003|1|/
#3|3|6|3|6|5|4|5|3|3|/
 4|cola_d|
#2003|1|1003|4|/
#8|7|10|9|8|11|/

Notes on Example: Control File for a Bulk Load of Cola Market Geometries:

The EXTERNAL keyword in the definition mkt_id INTEGER EXTERNAL means that each value to be inserted into the MKT_ID column (1, 2, 3, and 4 in this example) is an integer in human-readable form, not binary format.
In the data after begindata, each MKT_ID value is preceded by one space, because the CONTINUEIF NEXT(1:1) = '#' specification causes the first position of each data line to be ignored unless it is the number sign (#) continuation character.

Example: Control File for a Bulk Load of Polygons

Example: Control File for a Bulk Load of Polygons assumes that a table named POLY_4PT was created as follows:

CREATE TABLE POLY_4PT (GID VARCHAR2(32),
                       GEOMETRY SDO_GEOMETRY);

Assume that the ASCII data consists of a file with delimited columns and separate rows fixed by the limits of the table with the following format:

geometry rows:    GID, GEOMETRY

The coordinates in the GEOMETRY column represent polygons. Example: Control File for a Bulk Load of Polygons shows the control file for loading the data.

LOAD DATA
 INFILE *
 TRUNCATE
 CONTINUEIF NEXT(1:1) = '#'
 INTO TABLE POLY_4PT
 FIELDS TERMINATED BY '|'
 TRAILING NULLCOLS (
  GID  INTEGER EXTERNAL,
  GEOMETRY COLUMN OBJECT
   (
     SDO_GTYPE       INTEGER EXTERNAL,
     SDO_ELEM_INFO   VARRAY TERMINATED BY '|/'
       (elements     FLOAT EXTERNAL),
     SDO_ORDINATES   VARRAY TERMINATED BY '|/'
       (ordinates    FLOAT EXTERNAL)
   )
)
begindata
 1|2003|1|1003|1|/
#-122.4215|37.7862|-122.422|37.7869|-122.421|37.789|-122.42|37.7866|
#-122.4215|37.7862|/
 2|2003|1|1003|1|/
#-122.4019|37.8052|-122.4027|37.8055|-122.4031|37.806|-122.4012|37.8052|
#-122.4019|37.8052|/
 3|2003|1|1003|1|/
#-122.426|37.803|-122.4242|37.8053|-122.42355|37.8044|-122.4235|37.8025|
#-122.426|37.803|/

Bulk Loading Point-Only Data in SDO_GEOMETRY Objects

Example: Control File for a Bulk Load of Point-Only Data shows a control file for loading a table with point data. (The point coordinates happen to be in San Francisco, California, and reflect the Longitude/Latitude (WGS 84) coordinate system.)

Example: Control File for a Bulk Load of Point-Only Data

LOAD DATA
 INFILE *
 TRUNCATE
 CONTINUEIF NEXT(1:1) = '#'
 INTO TABLE POINT
 FIELDS TERMINATED BY '|'
 TRAILING NULLCOLS (
  GID      INTEGER EXTERNAL,
  GEOMETRY COLUMN OBJECT
   (
     SDO_GTYPE       INTEGER EXTERNAL,
     SDO_POINT COLUMN OBJECT
       (X            FLOAT EXTERNAL,
        Y            FLOAT EXTERNAL)
   )
)

BEGINDATA
 1| 2001| -122.4215| 37.7862|
 2| 2001| -122.4019| 37.8052|
 3| 2001| -122.426| 37.803|
 4| 2001| -122.4171| 37.8034|
 5| 2001| -122.416151| 37.8027228|