using different ORA_TZFILE per connection

[mdobrzanski]

1. What versions are you using?

• platform.platform: Linux-4.18.0-348.7.1.el8_5.x86_64-x86_64-with-centos-8.5.2111
• sys.maxsize > 2**32: True
• platform.python_version: 3.6.8
• cx_Oracle.version: 8.3.0
• cx_Oracle.clientversion: (21, 6, 0, 0, 0)

2. Describe the problem

I don't find a way to fetch timestamp with timezone from multiple oracle servers that use different timezone files.

I'm aware of the task about timezone https://github.com/oracle/python-cx_Oracle/issues/13 but that problem is different.

I found that ORA_TZFILE must be identical on server and on client otherwise error ORA-01805: possible error in date/time operation is raised.

What is the correct way to fetch timestamp with time zone for servers that use different time zone files? Is there a way to pass the ORA_TZFILE when connection is created?

3. Include a runnable Python script that shows the problem.

In below example I connect to docker containers with oracle servers 11, 19 and 21. ORA_TZFILE is timezone_35.dat and matches the file on Oracle 21 server.

There are two problems:

1. From Oracle 21 it fetches the timestamp with timezone just fine, but I can't do the same with other Oracle 19 or 11 servers. To do so I must change the environment variable ORA_TZINFO but then it fails for Oracle 21.
2. After error is raised on oracle 19 or 11, attempt to run again on  oracle 21 starts to raise error

def cx_oracle_execute(
        dsn, user='system',
        password='oracle',
        sql="select systimestamp at time zone 'America/Montreal' as d from dual"
):
    import cx_Oracle
    with cx_Oracle.connect(user=user, password=password, dsn=dsn) as conn:
        conn.stmtcachesize = 0
        cursor = conn.cursor()
        sql = "select systimestamp at time zone 'America/Montreal' as d from dual"
        for row in cursor.execute(sql):
            print(row)

# Oracle 21
# Returns timestamp in proper time zone.
# Timezone files is the same on server and client.
cx_oracle_execute('192.168.2.3:1621/ORCLCDB')

# Raises error for Oracle 19 or 11
# ORA-01805: possible error in date/time operation
# Servers use different time zone files than client.
cx_oracle_execute('192.168.2.3:1619/ORCLCDB')
cx_oracle_execute('192.168.2.3:1511/xe')

# Oracle 21, again
# It starts to raise the error like attempt with Oracle 19 or 11
# ORA-01805: possible error in date/time operation
cx_oracle_execute('192.168.2.3:1621/ORCLCDB')

[stale[bot]]

This issue has been automatically marked as inactive because it has not been updated recently. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not been updated for a month.