SQL*Loader

From: swaxolez <willem_at_pcfish.ca>
Date: 22 May 2006 19:15:09 -0700
Message-ID: <1148350509.853143.167440@g10g2000cwb.googlegroups.com>

Is it possible to have sql*loader repeat field data when importing from and external table.

i.e.

I have the following control file:

LOAD DATA
INFILE A2.CSV
APPEND INTO TABLE HYD_DATA
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS(
  CRDATE DATE "DD/MM/YY",
  CRTIME,
  PSI,
GEOMETRY COLUMN OBJECT
   (
   SDO_GTYPE CONSTANT 2001,
   SDO_SRID CONSTANT 2000097,

     SDO_POINT COLUMN OBJECT
       (Y            FLOAT EXTERNAL,
        X            FLOAT EXTERNAL)

   ),
  HEADING,
  SPEED,
  MODE,
  TYPE,
  FID
)

A record from the text file being loaded looks like this:

30/04/06,16:01:54,806,55.4566166,-129.6359816,157.1,0,0,0,120

The problem is that when I run the control file it converts the latitude and longitude into a spatial SDO_GEOMETRY column, in this case, GEOMETRY, but I'm left with null values in the Latitude and Longitude columns in my Oracle database.

So the Oracle Database looks like this:

CRDATE,CRTIME,PSI,LATITUDE,LONGITUDE,HEADING,SPEED,MODE,TYPE,FID,GEOMETRY 30/04/06,16:01:54,806,,,157.1,0,0,0,120,<data type>

I need to have the SQL*LOADER import the same fields into both the GEOMETRY column and the latitude and longitude columns.

Thanks,

Willem Received on Mon May 22 2006 - 21:15:09 CDT