Oracle ORA-01805 in Oracle 11g Database

Question

Our Oracle 10g database has recently been updated to 11g. The database runs on a computer running Windows Server 2003 X64. In SQL queries from a .NET application that access a table with TIMESTAMP (6) WITH TIME ZONE data columns, I get the following exception.

System.Data.OracleClient.OracleException: ORA-01805: possible error in date / time operation

the proposed action for exclusion is to ensure that the client and server are the same version:

ORA-01805: Possible error in the date / time operation Cause. The time zone files on the client and server do not match. The operation may lead to incorrect results based on the local time zone file. Action: Verify that the time zones of the client and server are the same.

I performed the following queries to check the time zone in the corresponding database. I did not find information on how to configure the time zone (or change the time zone 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 referencing the Instant Client that I installed, which is version 11_2? I execute queries through System.Data.OracleClient.OracleConnection as provided by the .NET Framework. U I assume that the “time zone version” refers to the time zone file versions. I do not see where the instant client has a timezone file. Any suggestions can be added.

Answer 1

Use "genezi -v" to find out the version of the time zone file.

Here is an example in my Linux box:

 $ genezi -v Client Shared Library 32-bit - 11.2.0.2.0 System name: Linux Release: 2.6.32-34-generic Version: #77-Ubuntu SMP Tue Sep 13 19:39:17 UTC 2011 Machine: x86_64 Operating in Instant Client mode. Small timezone file = timezone_14.dat Large timezone file = timezlrg_14.dat 

Answer 2

I determined that I have version 11_2_0_1 of the client installed. Upgrading to 11_2_0_2 seems to have eased this problem. However, I still do not understand how the instant client manages this time zone file, or even where it is or what it is. All the sources that I read say to make sure that the client and server have the same version of the time zone file, but it is not clear to me how this is done on the client. Perhaps this is not something that I can directly support outside of using another version of the client?