Load Timestamp with milliseconds into Oracle
Posted: Fri Apr 25, 2008 4:39 pm
I have a simple job to read from a sequential file and load into Oracle table.
One of the column in the file in timestamp in the format.
2008-04-01 02:05:48.147
I would like to maintain the same format in the target table
If I need to load it into an Oracle table -
1. What datatype should the target column be Date or Timestamp?
2. I create the table with target column as Timestamp, but I get the below error while loading into the table
ORA-01830: date format picture ends before converting entire input string
3. I manipulated the insert statement as below and got it to load into the table.
Is there any other way to load into the table without manipulating the insert script in the target OCI stage?
Any help appreciated.
One of the column in the file in timestamp in the format.
2008-04-01 02:05:48.147
I would like to maintain the same format in the target table
If I need to load it into an Oracle table -
1. What datatype should the target column be Date or Timestamp?
2. I create the table with target column as Timestamp, but I get the below error while loading into the table
ORA-01830: date format picture ends before converting entire input string
3. I manipulated the insert statement as below and got it to load into the table.
Code: Select all
TO_timestamp(:8, 'YYYY-MM-DD HH24:MI:SS.FF3')
Any help appreciated.