SQL*Loader

[swaxolez]

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

[Vladimir M. Zakharychev]

"swaxolez" <wil...@pcfish.ca> wrote in message
news:1148350509.8...@g10g2000cwb.googlegroups.com...

You can reference columns of currently processed record
using SQL expressions and bind variables to populate other
columns, so something like this should work:

(
...
PSI,
LATITUDE FLOAT EXTERNAL,
LONGITUDE FLOAT EXTERNAL,

GEOMETRY COLUMN OBJECT
(
SDO_GTYPE CONSTANT 2001,
SDO_SRID CONSTANT 2000097,
SDO_POINT COLUMN OBJECT

(Y ":LATITUDE",
X ":LONGITUDE")
),
HEADING,
...
)

Hth,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com