Load Timestamp with milliseconds into Oracle

[Dsnew]

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.

Code: Select all

TO_timestamp(:8, 'YYYY-MM-DD HH24:MI:SS.FF3')

Is there any other way to load into the table without manipulating the insert script in the target OCI stage?

Any help appreciated.

[kumar_s]

You are on the right method. If you define the field as timestamp in Datastage, it would certainly require this type casting before loading.

[Dsnew]

Kumar, sorry for the late reponse.
Yeah I guess you are right, that may be my only option. Thanks