Dear experts,
I am uanble to load the below timestamp column via sql loader
|03-JUL-2017 23:33:32| -----> loading failed
|03-JUL-2017 08:33:32| ---> Able to load succesfully
|03-JUL-2017 09:33:32| ---> Able to load succesfully
|03-JUL-2017 18:33:32| -----> loading failed
load data
INFILE 'D:\date_1.txt'
BADFILE 'D:\date_1.bad'
APPEND into table date_table
fields terminated by '|'
TRAILING NULLCOLS
(
TRAN_DTTM "TRIM(TO_CHAR(TO_DATE(:TRAN_DTTM,'DD-MM-YY HH:MI:SS'),'DD-MM-YY HH24:MI:SS'))",
)
SELECT * FROM V$NLS_PARAMETERS;
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MM-RR
NLS_DATE_LANGUAGE ENGLISH
NLS_CHARACTERSET WE8MSWIN1252
NLS_SORT BINARY
NLS_TIME_FORMAT HH12:MI:SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MM-RR HH12:MI:SSXFF AM
NLS_TIME_TZ_FORMAT HH12:MI:SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MM-RR HH12:MI:SSXFF AM TZR
NLS_DUAL_CURRENCY Rs
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
Error i am getting
Record 22: Rejected - Error on table date_table.
ORA-00604: error occurred at recursive SQL level 1
ORA-01843: not a valid month
I Wamt data to be inserted like
03-07-17 23:33:32.000000000
03-07-17 08:33:32.000000000
03-07-17 09:33:32.000000000
03-07-17 18:33:32.000000000