DSXchange

Hi,
I have a flat file input with a datetime column. The input data will have the format mm/dd/yyyy hh:mm:ss. The target is oracle database. When I tried to load data, I am getting the error Datetime field overflow. I have chosen timestamp as the datatype in DataStage and date in the target Odbc stage. It is a direct load. Please let us know how to populate timestamp value in Oracle.

which ora-Stage do you use (OCI or Bulk)?

first load it to sequencial file and then load it to oracle it will work

if you are able to define the format of the field in the flat file,
use YYYY-MM-DD HH:MM:SS
this is the format that datastage automtically recognizes for timestamp fiels.
The field in the oracle database can be date (which also accepts time-indications). You don't have to specify any formats using these settings.

in built routines are there to convert and to load oracle check path routines--sdk--date

use this i got same problem

routinename='rtDateTimeStamToOraOCIWithTime'

If Arg1 Then
Ans=Arg1[1,19]
end else
Ans=Arg1
End

use this in one routine and pass the time stap to the target
it will work

Already it is in a sequential file. Even if I give mm/dd/yyyy without timestamp, it gives me overflow error. I am using ODBC for ORACLE.

What is the ORA code that you are getting? Also tell us, what is the length and scale that you are using for this particular field.

You can use DateTimeStampToOraOCIWithTime(DateGenericToTimeStamp(link.inputcolumnname)) in the derivation - it should insert the right timestamp in the oracle target column.. This works for me