Re: SQL*Loader 2 fields->1 field

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/30
Message-ID: <34345dad.33170056@newshost>#1/1

On Mon, 29 Sep 1997 12:24:47 -0700, Vincent Chang <vchang_at_impac.com> wrote:

>Hi, Everybody
> I am using SQL*Loader to load a flat text file data. I need to load
>2 fields (time and date) into 1 field in table (oracle datatype 'date'
>can hold these 2 info in 1 field). Any suggestion for this problem?
>Thanks.
>
>vincent

You say flat text file. do you mean positional data (fixed length records?) if so and the DATE and TIME fields are not contigous, something like the following will work:

LOAD DATA
INFILE *
REPLACE
INTO TABLE TEST
( field1 POSITION(01:04) char,
  date_field POSITION(06:26) CHAR
"to_date(substr(:date_field,1,8)||substr(:date_field,14),'dd/mm/yyhh24:mi:ss')",

  field2        POSITION(15:17) char,
  field3        POSITION(28:30) char

)
BEGINDATA
abcd 01/12/96 def 14:15:01 ghi

We can use substr to piece together the date/time fields. If the fields are contigous in the input file, then the following will work as well:

LOAD DATA
INFILE *
REPLACE
INTO TABLE TEST

( field1        POSITION(01:04) char,
  date_field    POSITION(06:22) DATE "dd/mm/yy hh24:mi:ss",
  field2        POSITION(15:17) char,
  field3        POSITION(28:30) char

)
BEGINDATA
abcd 01/12/96 14:15:01 ghi

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 30 1997 - 00:00:00 CDT