Oracle ORA-01805 on Oracle 11g database

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 -vClient Shared Library 32-bit - 11.2.0.2.0System name:    LinuxRelease:    2.6.32-34-genericVersion:    #77-Ubuntu SMP Tue Sep 13 19:39:17 UTC 2011Machine:    x86_64Operating in Instant Client mode.Small timezone file = timezone_14.datLarge 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.1Collecting 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.0Successfully 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),)