Error while viewing data from OCI Stage

Dsnew · Post by **Dsnew** » Sat Nov 06, 2010 2:24 am

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.

chulett · Post by **chulett** » Sat Nov 06, 2010 8:05 am

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.

narasimha · Post by **narasimha** » Sat Nov 06, 2010 2:29 pm

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

Dsnew · Post by **Dsnew** » Sun Nov 07, 2010 11:34 am

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