I have a script creating a txt file which I want to import into an oracle table using sqlldr
but it is erroring with a ORA-01841: (full) year must be between -4713 and +9999, and not be 0
data
Code:
201403141218,carlo.lambertini,rdp-tcp#9,2,Active,1,14/03/2014 07:25,eu1ptsw001
201403141218,horst.saendler,rdp-tcp#1,3,Active,0,14/03/2014 05:45,eu1ptsw001
ctl file
Code:
options (skip=0)
Load Data
append into table rfgdba.TS_SESSION_SNAPSHOT
fields terminated by ',' trailing nullcols
(
snapshot_timestamp DATE "YYYYMMDDHH24MI",
username,
session_name,
id,
state,
idle_time,
logon_time DATE "DD/MM/YYYY HH24:MI",
ts_name
)
table definition
Code:
CREATE TABLE RFGDBA.TS_SESSION_SNAPSHOT
(
SNAPSHOT_TIMESTAMP DATE,
USERNAME VARCHAR2(30 BYTE),
SESSION_NAME VARCHAR2(30 BYTE),
ID NUMBER,
STATE VARCHAR2(10 BYTE),
IDLE_TIME NUMBER,
LOGON_TIME DATE,
TS_NAME VARCHAR2(30 BYTE)
)
I have tried (via sqlplus) - select to_date('201403141218', 'YYYYMMDDHHMI') from dual;
which returns a value of - 14/03/2014 12:18:00
so I cannot fathom why this is erroring