Re: SQL*Loader

From: Vladimir M. Zakharychev <bob--nospam--_at_dynamicpsp.com>
Date: Tue, 23 May 2006 13:11:55 +0400
Message-ID: <e4ujkt$2qr8$1@hypnos.nordnet.ru>

"swaxolez" <willem_at_pcfish.ca> wrote in message news:1148350509.853143.167440_at_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

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 Received on Tue May 23 2006 - 04:11:55 CDT