oracle sqlldr time format

Question

I'm using oracle sqlldr (for bulk load operations), but I can't convert this datetime format (first column):

File contents:

Jan  1 1900 11:36:56:000PM|968|409|198|33|30|45|19
Jan  1 1900 11:36:57:000PM|967|415|198|34|33|43|21
Jan  1 1900 11:36:59:000PM|966|427|197|34|33|40|19

Control file contents:

load data
infile '/home/bim/oraload/data/AERO.SONDAJ.samsun.txt'
append
into table AERO.SONDAJ
fields terminated by "|" 
TRAILING NULLCOLS
(
refsaat date 'MON DD YYYY HH24:mi:ss', --not running
bsnsvy, 
yuks, 
sck, 
nem, 
isba, 
rzgyon, 
rzghiz
)

What is the datatype of the column the data is going in to? What is the exact error you get from the log file? "not running" does not give enough information. — Gary_W

cableload cableload · Accepted Answer · 2016-04-26T15:16:39

Try something like this. Inorder for this to work, the refsaat type should be a timestamp type and not DATE data type. Date Data type does not store beyond seconds.

refsaat TIMESTAMP 'Mon DD YYYY HH:mi:ss:ff3PM'

oracle sqlldr time format

1 Answers