Add support for named time zones in thin mode

[mdobrzanski]

Fetching timestamp with time zone raises error ORA-01805: possible error in date/time operation instead of returning datetime with time zone information.

1. What versions are you using?

   • database version: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
   • platform.python_version: 3.9.9
   • oracledb.__version__: 1.0.1

2. Is it an error or a hang or a crash? Error

3. What error(s) or behavior you are seeing?

Error oracledb.exceptions.DatabaseError: ORA-01805: possible error in date/time operation is raised when trying to use at time zone in SQL . Below is a sample code to reproduce the problem.

5. Does your application call init_oracle_client()? Yes, uses Thick mode.

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

import oracledb
from oracledb.thick_impl import init_oracle_client


def f(sql):
    with oracledb.connect(user='hr', password='password', dsn='127.0.0.1:1511/xe') as connection:
        with connection.cursor() as cursor:
            query = cursor.execute(sql)
            row = query.fetchone()
            print(row[0].time())


init_oracle_client()

# this works because returns datetime without time zone
f("select systimestamp from dual")

# this raises error ORA-01805
f("select systimestamp at time zone 'America/Montreal' as d from dual")


# this is manual walkaround but datetime is still missing time zone information
f("select cast(systimestamp at time zone 'America/Montreal' as timestamp) as d from dual")

The above is an example using systimestamp but similar error can be achieved by crating table with column type timestamp with time zone

create table tz_table
(
	tz_timestamp TIMESTAMP(6) WITH TIME ZONE not null
);

insert into tz_table values ( TIMESTAMP '2022-06-16 08:00:00 US/Central');

[cjbj]

This is an Oracle Client library issue. We saw the same problem once in the past for cx_Oracle with an application using timezone v35 files. There was no problem when that application used v36 files. We logged it as Oracle bug 33576821, which is still open - since there was a simple solution.

If you can share any other details such as machine timezone settings and environment variables I can add it to the bug to help identify what triggers it.

Some thoughts:

• Try grabbing a recent Instant Client and using those libraries. This may or may not help, since you'll need 19c (or earlier) client to connect to 11.2 DB, and the latest 19.15 client has the timezone v32 files.
• It's time to upgrade your Oracle DB. The latest XE is 21c https://www.oracle.com/au/database/technologies/xe-downloads.html

[mdobrzanski]

I've done some more testing and found that the timestamp with time zone works correctly when ORA_TZFILE is the same on server and client. I had similar problems with cx_Oracle and posted there a question oracle/python-cx_Oracle#631

I couldn't find v36 files. Older versions I found in official docker images. @cjbj could you point me where I could the v36 from? Maybe v36 in client would work correctly with older server time zone versions.

Interesting is that with client 21 I was able to connect to server 11 although the official OCI download page says different

Oracle Call Interface 21 can connect to Oracle Database 12.1 or later, while Oracle Call Interface 19.3 can connect to Oracle Database 11.2 or later.

Below OCI 21.6

>>> import oracledb
>>> from oracledb.thick_impl import init_oracle_client
>>> init_oracle_client()
>>> with oracledb.connect(user='hr', password='password', dsn='127.0.0.1:1511/xe') as connection:
...     with connection.cursor() as cursor:
...         cursor.execute('select 1 from dual').fetchone()
... 
(1,)

[anthony-tuininga]

What I have discovered is that everything works as expected with numeric timezone data (-05:00 instead of US/Central, for example). The thin driver doesn't handle named time zones properly either -- so that needs to be addressed (either raising an error like the thick driver or something better than that if possible!). The problem with using named time zones is that everyone has to agree on what they mean. I'll ask internally if there is a way to mitigate this.

[cjbj]

The v36 files were probably from an internal build.

Regarding 21->11 connectivity, there's sometimes a difference between what's technically possibly vs what is 'supported' in the tested & "you can call us if there is a problem" way.

[agilevic]

@anthony-tuininga @cjbj are we in agreement that currently the only viable work-around is to use numerical hour offset?
Also, can you share where can we track progress of the issue reported with Oracle client library (33576821)?

[cjbj]

@agilevic Any decision on named timezones is still pending. And so is that bug - sorry.

[anthony-tuininga]

@agilevic, the current viable workaround for the issue is to use numerical time zone offsets when using thin mode. I will probably add an exception for version 1.2.3 and will then discuss further what to do for version 1.3 -- watch this space!

[agilevic]

Thank you, both. For what it's worth our workaround has been to cast TIMESTAMP WITH TIMEZONE, which might be required in query logic (join) to regular TIMESTAMP in the SELECT clause for the output. In many cases this is sufficient in the client application, but of course only covers some use cases and can slow down query performance.

[khooz]

Having the same issue using thick mode.

• OS: Windows Server 2022 Standard 21H2 build 20348.1668
• Instant client version: 21.10.0.0
• ora_tzfile: timezlrg_41.dat
• ora_sdtz: DB_TZ
• DB version: 19.15.0.0.0
• DB tzfile: timezlrg_41.dat
• oracledb mode: Thick
  I don't know why named zones don't work even using the latest instant client dll in thick mode using (almost) latest tzdata file and the large timezone file. Doesn't the module use all the instant client features? The same instant client works for PHP OCI and SQLDeveloper. I'd be happy to provide any necessary information to help resolve this faster.

the code

import sys
import os
import platform
import oracledb
import pandas as pd
import sqlalchemy
import warnings
warnings.filterwarnings("ignore")

class OracleConnector(object):
    def __init__(self, user : str = None, password : str = None, host : str = None, port : int = None, service_name : str = None, lib_dir : str = None):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.service_name = service_name
        self.thick_mode = {"lib_dir": lib_dir}
        if (self.user is None):
            self.user = 'test'
        if (self.password is None):
            self.password = 'test'
        if (self.host is None):
            self.host = '127.0.0.1'
        if (self.port is None):
            self.port = 1521
        if (self.service_name is None):
            self.service_name = 'xe'
        # if (self.thick_mode['lib_dir'] is not None):
        #     oracledb.init_oracle_client(lib_dir=self.thick_mode['lib_dir'])

    def run_query(self, query):
        engine = sqlalchemy.create_engine(
            f'oracle+oracledb://{self.user}:{self.password}@{self.host}:{self.port}/?service_name={self.service_name}',
            thick_mode=self.thick_mode)

        with engine.connect() as connection:
        # with oracledb.connect(user = self.user, password = self.password,
        #             host = self.host, port = self.port,
        #             service_name = self.service_name) as connection:
            return pd.read_sql(query, con = connection)


def main():
    x = OracleConnector()
    sql = """
        select to_timestamp_tz('2023-07-05 02:00:00.000000000 America/Montreal', 'YYYY-MM-DD HH24:MI:SS.FF9 TZR') x from dual
    """
    df = x.run_query(sql)
    print(df)

if __name__ == '__main__':
    main()

[damilareisaac]

I ran into this issue. Upgrade from oracle_cx. It is not possible to update the oracle database as it is serving a legacy application.
Now getting the exception message: _named time zones are not supported in thin mode

@mapper_registry.mapped
@dataclass
class SampleTable:
     __tablename__ = "sample_table"
     date_created: datetime = field(metadata={"sa": Column(TIMESTAMP(False), nullable=False)} )

Any solution on how to fix this?

[anthony-tuininga]

You can use thick mode to fetch timestamp with timezone using named time zones or you can use numeric offsets. The support for named time zones in thin mode is on a long list of enhancement requests!