DSXchange

I am facing an issue with reading and writing dates from/into Oracle

My job design
Source_OCI_Stage -> Transformer -> Target_OCI_Stage

1. The Oracle table has the datatype as DATE
2. OCI Stage has the column defined as TIMESTAMP

When I click on view data I get the error ORA-01821: date format not recognized

I am using generated sql
Source_OCI_Stage corresponds to TO_CHAR(MYCOL,'YYYY-MM-DD HH24:MI:SS')
Target_OCI_Stage corresponds to TO_DATE(MYCOL,'YYYY-MM-DD HH24:MI:SS')

We are using Oracle 11.2.0.1

Help Appreciated.

All that sounds good, so unsure why you would be gettting that error. I would suspect either a bug or some kind of issue with 11g, since I don't think it is 'officially' supported. First thing I'd check is that you were up-to-date on your patches, and they can be plentiful. I'm also curious if the job runs ok and the issue is only a View Data issue, or if there are issues all around.

Probably best to involve your official support provider, unless someone here can add some specifics.

I had the same issue - The solution is to introduce a new Environment Variable - DS_NO_FF/String/Variable/1

Thanks narasimha and chulett. That did it! Appreciate your time and knowledge.