Using sqlldr with millseconds

[illectronic]

Hi,

I have some data in this format:
2011-01-31 00:00:00.000

I am getting "invalid date format" when I tried to load the file using: DATE "YYYY-MM-DD HH24:MI:SS.FF3"

How can I load it?'

Thanks

 

[Dagon]

You need something like:

LOAD DATA
INFILE 'fred.dat'
REPLACE
INTO TABLE FRED
TRAILING NULLCOLS (
tscol position(1:25) char "TO_TIMESTAMP (:tscol, 'YYYY-MM-DD HH24:MI:SS.FF3')"
)

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

illectronic,

In addition to Dagon's excellent suggestion, have you considered using Oracle's External Tables? The feature allows you to access flat files as though they are full-fledged Oracle tables without your having to separately invoke Oracle*Loader. The other nice thing about External Tables is that if you replace the underlying flat file, but use the same file name, subsequent queries instantly show you the "new" data.

Let us know if this proves helpful to you.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[illectronic]

I didn't have to use the ms at the end because all that data was 0, but thanks a lot! I can use this in the future.