Re: SQL LOADER HELP HELP HELP

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 30 Sep 1999 09:42:16 -0400
Message-ID: <cmjzN2ITCwx0CLNNbtAQwQ=sSu2t_at_4ax.com>

A copy of this was sent to "Claudio Lombardo" <clombardo_at_bsc.it> (if that email address didn't require changing) On Thu, 30 Sep 1999 13:59:16 +0200, you wrote:

>Hi everybody,
>I have to load a lot of ascii files written like the following example:
>
>0|2048|1008|0|Rete12|010|09/28/1999 01:00:37 GMT+02:00|09/28/1999 01:55:37
>GMT+02:00
>0|2048|1008|0|Rete12|010|09/28/1999 01:04:47 GMT+02:00|09/28/1999 01:59:48
>GMT+02:00
>1873|161|5485|0|Rete12|010|09/28/1999 01:02:37 GMT+02:00|09/28/1999 01:02:50
>GMT+02:00
>0|2048|1008|0|Rete12|010|09/28/1999 01:04:02 GMT+02:00|09/28/1999 01:59:02
>GMT+02:00
>0|2048|1008|0|Rete12|010|09/28/1999 01:04:01 GMT+02:00|09/28/1999 01:59:02
>GMT+02:00
>
>In Oracle 8.04 for NT I have defined the following table:
>

it could look like this:

LOAD DATA
INFILE test.dat
APPEND INTO TABLE VU
FIELDS TERMINATED BY '|'
(SRCPORT,

DSTPORT,
TXBYTES,
RXBYTES,

LOGIN,
ACCTNUM,
STARTTIME date 'mm/dd/yyyy hh24:mi:ss "GMT+02:00"', ENDTIME date 'mm/dd/yyyy hh24:mi:ss "GMT+02:00"' )

that will use load the date September 28'th, 1999 1:55:37am for the first record. It ignores the GMT+02:00 portion of your input (which is just telling you the time is GMT+2 hours).

>SrcPort VARCHAR2(15)
>DstPort VARCHAR2(15)
>TxBytes NUMBER (15,0)
>RxBytes NUMBER(15,0)
>Login VARCHAR2 (30)
>AcctNum NUMBER (5,0)
>StartTime DATE
>EndTime DATE.
>
>I have tried to load via SQLLDR80 utility this data in my table (called
>"vu"), using this control file:
>
>LOAD DATA
>INFILE 'd:\temp\pippo.dat'
>APPEND INTO TABLE MYUSER.VU
>FIELDS TERMINATED BY '|'
>(SRCPORT,
>DSTPORT,
>TXBYTES,
>RXBYTES,
>LOGIN,
>ACCTNUM,
>STARTTIME DATE "????????",
>ENDTIME DATE "????????")
>
>
>How I have to substitute the "????" in order to load correctly the DATE
>information stored into ascii file (note that I need only date and time and
>not the GMT information).
>
>Please answer as soon as possible... I'M DISPERATE!!!!!!
>
>Thank you !
>
>CLaudio

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Thu Sep 30 1999 - 15:42:16 CEST