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.

---

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?

---

Use "genezi -v" to know the timezone file version.

Here is a sample 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

---

Beside other reasons, the problem occurs also using python3.6, when you're doing a timezone conversion and using cx-Oracle libraries with much higher version than database.

It explains the comment from "Manuel Pinot". As soon as he comments the TZ conversion line, it works.

I had the same issue with python 3.6 using most recent cx-Oracle 8.1.0, but connected to old database 12.1.

• Used python: python3.6
• Installed most recent cx-Oracle Version 8.1.0 (usually for releases around 18-19.x)
• Connected database: 12.1.0.2
• Oracle instant client 12.2

Calling a query without timezone conversion works fine:

sql='''select cast (sysdate AS TIMESTAMP WITH TIME ZONE) from dual'''
cur.execute(sql)
cur.fetchone()
(datetime.datetime(2020, 12, 28, 17, 7, 52),)

Calling similar query with timezone conversion fails:

sql='''select cast (sysdate AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' from dual'''
cur.execute(sql)
cur.fetchone()
*** cx_Oracle.DatabaseError: ORA-01805: possible error in date/time operation

Solution in my case: Downgrade to older cx-Oracle client (Oracle-instant client 12.2 keeps unchanged)

pip install -U cx-Oracle==6.4.1
Collecting cx-Oracle==6.4.1
  Using cached cx_Oracle-6.4.1-cp36-cp36m-manylinux1_x86_64.whl (596 kB)
    Installing collected packages: cx-Oracle
  Attempting uninstall: cx-Oracle
    Found existing installation: cx-Oracle 8.1.0
    Uninstalling cx-Oracle-8.1.0:
      Successfully uninstalled cx-Oracle-8.1.0
Successfully installed cx-Oracle-6.4.1

Now testing again with timezone conversion:

sql='''select cast (sysdate AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' from dual'''
cur.execute(sql)
cur.fetchone()
(datetime.datetime(2020, 12, 28, 17, 20, 54),)

---

I have the same problem whit ORA-01505 in Oracle 11G using Docker

ActiveRecord::StatementInvalid (OCIError: ORA-01805: possible error in date/time operation: SELECT  "USERS".* FROM "USERS" WHERE "USERS"."EMAIL" = :a1 ORDER BY "USERS"."ID" ASC FETCH FIRST :a2 ROWS ONLY):

I was using in the docker-compose.yml

    environment:
      - TZ=America/Guatemala

So just comment the line and every thing works

      environment:
      #- TZ=America/Guatemala