Re: SQL Loader Question

Either drop the fractional seconds from the ASCII file or load everything into a temp table and then parse it out from there. If not too many records ... use UTL_FILE rather than SQL*Loader.

Daniel Morgan

Eric Levinson wrote:

> I am trying to load in a comma delimited flat file, where the a date column
> in the file is:
>
> YYYY-MM-DD HH:MI:SS.SSS
>
> Where the .SSS is a fractional seconds. I essentially want to tell SQL
> Loader to IGNORE everything after the period (since I can't seem to find a
> TO_DATE specifier for 10 thousandths of a second)
>
> I have tried everything in the SQL Loader book for accomplishing this, but
> to no avail.
>
> Currently, my control file looks like this:
>
> LOAD DATA
> INFILE "/extracts/tr/file.csv"
> INFILE TR_CUSTOMERXREF
> FIELDS TERMINATED BY ','
> (
> tr_customerxrefseq ,
> type,
> created DATE 'YYYY-MM-DD HH24:MI:SS' "SUBSTR(:created,1,19)",
> deleted,
> customerid,
> custseq
> )
>
> The data looks like this:
>
> 11167125,1,2002-02-08 11:08:04.793,0,32079355,35976194
> 11167126,1,2002-02-08 11:08:04.793,0,32079356,35976195
>
> When this runs, it looks like I get errors telling me that CUSTSEQ is not a
> number. My only guess it it is because it is trying to use the '.793' for
> the following columns.
>
> How can I make SQL Loader forget about the fractional seconds? or even
> better, does Oracle have a way of taking the fractional seconds?
>
> Any help would be greatly appreciated!
>
> Eric
>
> eric at levinsong dot com
Received on Thu Feb 14 2002 - 03:44:33 CET