Hello guys,
Here's the environment:
Oracle DB 11.2.0.1 on Linux 64bit RHEL
Oracle 11gr2 32bit client on Windows 2008 R2
Our product runs a service (code built in c++) and the service opens 5 database sessions and issues alter session set nls_date_format to HH24:MI:SS DD/MM/YYYY in each of them.
However looking through the logs of the service, I see that the select statements (to_char(date)) return the date in the format other than what was set via 'alter session' i.e. they do not show time part and just DD-MON-YY . But the same select statements output the date in the correct/expected format (as per alter session).
The problem is we do an insert into a table taking current system's time and performing to_date(<system time>). This is failing with : ORA-01843: not a valid month. I understand that we should specify a format for the input string i.e. system date but we do not and has not been an issue for anyone else.
I also see no session disconnections or re-connections.
The Oracle client NLS_LANG is : AMERICAN_AMERICA.UTF8.
This issue is happening to only one of our customers who uses this product. All other customers do not encounter this issue as this has been a long standing code. I'm not saying the code is perfect and from looking at some of the SQLs, we depend on Oracle's implicit data conversion rather than modifying the SQL but this has never caused an issue to any other customer.
I understand about the hierarchy of the setting of NLS_DATE_FORMAT i.e. database, client's OS registry, Oracle client (alter session) and finally to_date/to_char functions.
It appears that the session format set is getting overriden as when they restart the service in our product everything is back to normal until a few days when the queries in the service logs start showing the incorrect date without time part and just DD-MON-YY.
Any help would be appreciated as I'd been reading a lot on the internet with no clue apart from this site which our customer found:
h ttp://stackoverflow.com/questions/15396241/cx-oracle-ora-01843-not-a-valid-month-with-unicode-parameter
Thanks,
Bharath