Feb 152013
 

I received some files with only coordinates, which had to be loaded into Oracle. It’s not super difficult, but I thought I’d share it anyway. The files contain some comment lines first, easily recognized the the hash (#) as the first character. The rest of the lines are x, y and z separated by spaces, like this:

#[MetadataBegin]
#[OrderNumber]2013001
#[Measured by]Piet
#[MeasureDate]30-1-2013
#[MetadataEnd]
 58392.50 439762.50 -0.01
 58397.50 439762.50 -0.03
 58402.50 439762.50 -0.03
 58407.50 439762.50 -0.03

The control file should be like this (change the filename etc):

OPTIONS (DIRECT=TRUE,MULTITHREADING=TRUE)
LOAD DATA
INTO TABLE DEPTHDATA
WHEN 1-1<>'#'
FIELDS TERMINATED BY ' '
(ID SEQUENCE(1,1),
 GEOMETRY COLUMN OBJECT
 (
  SDO_GTYPE CONSTANT "3001",
  SDO_POINT COLUMN OBJECT
   (X FLOAT EXTERNAL TERMINATED BY ' ' ,
    Y FLOAT EXTERNAL TERMINATED BY ' ' ,
    Z FLOAT EXTERNAL TERMINATED BY ' ' )
   )
)

  2 Responses to “Loading 3D points into Oracle with SQL Loader”

  1. Is there a limit in file-size with this method? Can you load a file of 4Gb?

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)