SQL & PL/SQL

Hey Guys,

We are storing one date value (in DD-MON-RRR format) in one descriptive flex field of one seeded oracle apps table. It stores in ATTRIBUTE14 of that table, which is of type VARCHAR2. we are then using this field in our various custom PL/SQL codes.

We use to call it like this : select TO_DATE ( ATTRIBUTE14 , 'DD-MON-RRRR' ) from csi_item_instances WHERE INSTANCE_ID=10000

It works well across all the client store in US.

But recently we implemented a store in CHINA for the same client. To retrieve data in Chinese language we use to set NLS_LANGUAGE = 'SIMPLIFIED CHINESE' and NLS_TERRITORY = 'CHINA' . Something like as shown bellow.

ALTER SESSION SET NLS_LANGUAGE = 'SIMPLIFIED CHINESE' NLS_TERRITORY = 'CHINA'

Then if we run the same SQL or PL/SQL code.

select TO_DATE ( ATTRIBUTE14 , 'DD-MON-RRRR' ) from csi_item_instances WHERE INSTANCE_ID=10000

its trowing error : ORA - 01843: not a valid month / 01843.0000 - "not a valid month"

We cannot change the all the data because its used by US store as well. Please help me to find out is this a oracle bug or our own issue.

Any help in this ERROR will be appreciated.

Thanks,
Sambit