Oracle ORA-01805 on Oracle 11g database
Our Oracle 10g database was recently upgraded to 11g. The database is running on a Windows Server 2003 X64 machine. In SQL queries from a .NET application that access a table that has a TIMESTAMP (6) WITH TIME ZONE data columns, I am getting the following exception.
System.Data.OracleClient.OracleException : ORA-01805: possible error in date/time operation
The suggested action for the exception is to ensure that the client and server are the same version:
ORA-01805: possible error in date/time operation Cause: The timezone files on client and server do not match. Operation can potentially result in incorrect results based on local timezone file. Action: Please ensure client and server timezone versions are same.
I've ran the following queries to check the timezone on the database in question. I haven't found information on how I set the timezone (or change the timezone file) for the client.
SELECT dbtimezone FROM DUAL;
select * from v$timezone_file;
DBTIMEZONE
----------
+00:00
FILENAME VERSION
-------------------- ----------------------
timezlrg_14.dat 14
I assume the client is referring to the Instant Client I have installed, which is version 11_2? I'm running the queries through a System.Data.OracleClient.OracleConnection as provided by the .NET Framework. U I assume by "timezone version" it's referring to the timezone file versions. I don't see where the instant client has a timezone file. Any suggestions are appreicated.
Answer
I determined that I had version 11_2_0_1 of the instant client installed. Upgrading to 11_2_0_2 seems to have relieved this issue. However, I'm still not clear on how the instant client manages it timezone file, or even where it is or what it is. All the sources I've read say to ensure that the client and server have the same timezone file version, but it's not clear to me how that is actually done on the client. Perhaps it's not something I can directly maintain beyond using a different version of the instant client?