Error while trying to load timestamp data into oracle

[ds_ram]

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.

[WoMaWil]

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

[vijaydev]

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

[rverharen]

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.

[vijaydev]

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

[vijaydev]

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

[ds_ram]

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.

[DSguru2B]

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.

[jreddy]

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