Loading Timestamp to Oracle

clmhwyfe · Post by **clmhwyfe** » Tue Mar 17, 2015 5:22 pm

Hi all,
I have a job in DS 7.5 which reads from the sequential file and loads an oracle table, the file contains few timestamp columns in the format - May 1 2011 12:00AM.

The job runs fine and loads the data to oracle fine in 7.5 whereas in 8.7 am unable to read the file column as timestamp (I tried to change the default timestamp format to %mmm %d %yyyy %hh:%nn:%ss%aa).

I tried to change the datatype to VARCHAR and was able to read from the file but am unable to load the data to Oracle as the StringtoTimeStamp function is not able to convert the same to a timestamp while loading.

Please advise

chulett · Post by **chulett** » Tue Mar 17, 2015 5:42 pm

Is the datatype of your target field in Oracle a DATE or is it truly a TIMESTAMP?

clmhwyfe · Post by **clmhwyfe** » Tue Mar 17, 2015 5:56 pm

Hi ,

I checked the datatye in the target table and Its a DATE in the Oracle table

qt_ky · Post by **qt_ky** » Wed Mar 18, 2015 5:19 am

Oracle DATE type stores both date and time, I suppose because Oracle is "different" ...

chulett · Post by **chulett** » Wed Mar 18, 2015 7:23 am

Yes it does and yes it is.

You should be able to read it as a string and use the StringToTimestamp() function to match the "default format" of a date/time in your database, talk to your DBA if you don't know exactly what that is. Or take the value as you have it now and use a TO_DATE() in your insert DML and supply a format picture / mask that matches your incoming format so the insert itself will convert it properly.

Or play the standardize all Oracle DATE values game, something I always did 'back in the day'. I made sure my values in job matched an "ISO" timestamp format: YYYY-MM-DD HH24:MI:SS and then allowed the target stage to generate the DML, it knew to wrap any DATE values in a TO_DATE() automatically. Now, this was well before Connectors came on the scene so no clue if their "Generate SQL at runtime" option does any automatic conversions like that.

clmhwyfe · Post by **clmhwyfe** » Wed Mar 18, 2015 11:21 am

Thanks chulett,

I tried to use to_date(:11, 'MON DD YYYY HH12:MIPM')) but got the below error,

"Error code: 1858, Error message: ORA-01858: a non-numeric character was found where a numeric was expected "

There are nulls for a few records, Could it be because of 'NULLS' that i get the above error

chulett · Post by **chulett** » Wed Mar 18, 2015 12:05 pm

Possibly... as a test, remove them from the source and see. Or do a simple insert test in a tool like Toad to confirm you have the proper format mask for the TO_DATE function and your data.

clmhwyfe · Post by **clmhwyfe** » Wed Mar 18, 2015 2:02 pm

Hi chulett,

I got the issue now, when i move the file from the windows to unix environment it places a special character in the file for null and thats the reason the job is unable to load the data.

,^@,^@,

Its apending the above mentioned characters whereever the columns value is null. I tried to transfer the file via binary transfer mode but still am able to see these characters and the jobs aborts with this issue