while I load the value using sqlldr the millisecond values not stored in oracle table.
Value:
'26-OCT-17 08.59.50.916000000 AM'
CTL field:
SRC_SYS_CRT_TS Position(23:48) "decode(:SRC_SYS_CRT_TS,null,sysdate-1,to_timestamp(:SRC_SYS_CRT_TS,'yyyy-mm-dd.hh24.mi.ss.FF'))",
Expected output :
2017-10-26 08.59.50.916000000 AM
Current Output:
2017-10-26 08.59.50.000000000 AM
Please advise
Your input data does not match the format specified in your control file. Also convert sysdate-1 to timestamp:-
"decode(:SRC_SYS_CRT_TS, NULL, to_timestamp(sysdate-1), to_timestamp(:SRC_SYS_CRT_TS,'DD-MON-YY HH.MI.SS.FF AM'))")
---------- Post updated at 14:49 ---------- Previous update was at 12:40 ----------
Also note that NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP datatype.
So instead of converting, I would suggest simply specify the field is timestamp in the control file.
(SRC_SYS_CRT_TS TIMESTAMP 'DD-MON-YY HH.MI.SS.FF AM')