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 https://github.com/oracle/python-cx_Oracle/issues/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!

[anthony-tuininga]

I've changed this to an enhancement request so it can be tracked.

[damilareisaac]

Not working in thick mode either. got the error: sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-01805: possible error in date/time operation

I am not sure what you mean by numeric offset. if you can please give an example. I tried changing timestamp to Numeric in the ORM schema, got the error: FAILED tests/integration/test_api/test_ppts.py::test_get_all_ppts - sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTA...

[damilareisaac]

Full traceback

Using oracledb 2.0.1

.venv\Lib\site-packages\sqlalchemy\engine\cursor.py:1135:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\Lib\site-packages\oracledb\cursor.py:828: in fetchall
    row = fetch_next_row(self)
src\\oracledb\\impl/base/cursor.pyx:441: in oracledb.base_impl.BaseCursorImpl.fetch_next_row
    ???
src\\oracledb\\impl/thick/cursor.pyx:151: in oracledb.thick_impl.ThickCursorImpl._fetch_rows
    ???
src\\oracledb\\impl/thick/utils.pyx:428: in oracledb.thick_impl._raise_from_odpi
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   oracledb.exceptions.DatabaseError: ORA-01805: possible error in date/time operation

src\\oracledb\\impl/thick/utils.pyx:418: DatabaseError

The above exception was the direct cause of the following exception:

    def test_get_all_ppts():
>       response = client.get("/ppts/")

tests\integration\test_api\test_ppts.py:12:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
.venv\Lib\site-packages\starlette\testclient.py:523: in get
    return super().get(
.venv\Lib\site-packages\httpx\_client.py:1055: in get
    return self.request(
.venv\Lib\site-packages\starlette\testclient.py:491: in request
    return super().request(
.venv\Lib\site-packages\httpx\_client.py:828: in request
    return self.send(request, auth=auth, follow_redirects=follow_redirects)
.venv\Lib\site-packages\httpx\_client.py:915: in send
    response = self._send_handling_auth(
.venv\Lib\site-packages\httpx\_client.py:943: in _send_handling_auth
    response = self._send_handling_redirects(
.venv\Lib\site-packages\httpx\_client.py:980: in _send_handling_redirects
    response = self._send_single_request(request)
.venv\Lib\site-packages\httpx\_client.py:1016: in _send_single_request
    response = transport.handle_request(request)
.venv\Lib\site-packages\starlette\testclient.py:372: in handle_request
    raise exc
.venv\Lib\site-packages\starlette\testclient.py:369: in handle_request
    portal.call(self.app, scope, receive, send)
.venv\Lib\site-packages\anyio\from_thread.py:288: in call
    return cast(T_Retval, self.start_task_soon(func, *args).result())
..\..\..\..\..\.pyenv\pyenv-win\versions\3.12.1\Lib\concurrent\futures\_base.py:456: in result
    return self.__get_result()
..\..\..\..\..\.pyenv\pyenv-win\versions\3.12.1\Lib\concurrent\futures\_base.py:401: in __get_result
    raise self._exception
.venv\Lib\site-packages\anyio\from_thread.py:217: in _call_func
    retval = await retval_or_awaitable
.venv\Lib\site-packages\fastapi\applications.py:1054: in __call__
    await super().__call__(scope, receive, send)
.venv\Lib\site-packages\starlette\applications.py:123: in __call__
    await self.middleware_stack(scope, receive, send)
.venv\Lib\site-packages\starlette\middleware\errors.py:186: in __call__
    raise exc
.venv\Lib\site-packages\starlette\middleware\errors.py:164: in __call__
    await self.app(scope, receive, _send)
.venv\Lib\site-packages\starlette\middleware\exceptions.py:62: in __call__
    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)
.venv\Lib\site-packages\starlette\_exception_handler.py:64: in wrapped_app
    raise exc
.venv\Lib\site-packages\starlette\_exception_handler.py:53: in wrapped_app
    await app(scope, receive, sender)
.venv\Lib\site-packages\starlette\routing.py:758: in __call__
    await self.middleware_stack(scope, receive, send)
.venv\Lib\site-packages\starlette\routing.py:778: in app
    await route.handle(scope, receive, send)
.venv\Lib\site-packages\starlette\routing.py:299: in handle
    await self.app(scope, receive, send)
.venv\Lib\site-packages\starlette\routing.py:79: in app
    await wrap_app_handling_exceptions(app, request)(scope, receive, send)
.venv\Lib\site-packages\starlette\_exception_handler.py:64: in wrapped_app
    raise exc
.venv\Lib\site-packages\starlette\_exception_handler.py:53: in wrapped_app
    await app(scope, receive, sender)
.venv\Lib\site-packages\starlette\routing.py:74: in app
    response = await func(request)
.venv\Lib\site-packages\fastapi\routing.py:315: in app
    content = await serialize_response(
.venv\Lib\site-packages\fastapi\routing.py:160: in serialize_response
    return field.serialize(
.venv\Lib\site-packages\fastapi\_compat.py:147: in serialize
    return self._type_adapter.dump_python(
.venv\Lib\site-packages\pydantic\type_adapter.py:331: in dump_python
    return self.serializer.to_python(
.venv\Lib\site-packages\sqlalchemy\orm\attributes.py:566: in __get__
    return self.impl.get(state, dict_)  # type: ignore[no-any-return]
.venv\Lib\site-packages\sqlalchemy\orm\attributes.py:1086: in get
    value = self._fire_loader_callables(state, key, passive)
.venv\Lib\site-packages\sqlalchemy\orm\attributes.py:1119: in _fire_loader_callables
    return callable_(state, passive)
.venv\Lib\site-packages\sqlalchemy\orm\strategies.py:574: in __call__
    return strategy._load_for_state(state, passive)
.venv\Lib\site-packages\sqlalchemy\orm\strategies.py:541: in _load_for_state
    loading.load_scalar_attributes(
.venv\Lib\site-packages\sqlalchemy\orm\loading.py:1653: in load_scalar_attributes
    result = load_on_ident(
.venv\Lib\site-packages\sqlalchemy\orm\loading.py:509: in load_on_ident
    return load_on_pk_identity(
.venv\Lib\site-packages\sqlalchemy\orm\loading.py:705: in load_on_pk_identity
    return result.one()
.venv\Lib\site-packages\sqlalchemy\engine\result.py:1810: in one
    return self._only_one_row(
.venv\Lib\site-packages\sqlalchemy\engine\result.py:749: in _only_one_row
    row: Optional[_InterimRowType[Any]] = onerow(hard_close=True)
.venv\Lib\site-packages\sqlalchemy\engine\result.py:1673: in _fetchone_impl
    return self._real_result._fetchone_impl(hard_close=hard_close)
.venv\Lib\site-packages\sqlalchemy\engine\result.py:2259: in _fetchone_impl
    row = next(self.iterator, _NO_ROW)
.venv\Lib\site-packages\sqlalchemy\orm\loading.py:219: in chunks
    fetch = cursor._raw_all_rows()
.venv\Lib\site-packages\sqlalchemy\engine\result.py:540: in _raw_all_rows
    rows = self._fetchall_impl()
.venv\Lib\site-packages\sqlalchemy\engine\cursor.py:2103: in _fetchall_impl
    return self.cursor_strategy.fetchall(self, self.cursor)
.venv\Lib\site-packages\sqlalchemy\engine\cursor.py:1139: in fetchall
    self.handle_exception(result, dbapi_cursor, e)
.venv\Lib\site-packages\sqlalchemy\engine\cursor.py:1080: in handle_exception
    result.connection._handle_dbapi_exception(
.venv\Lib\site-packages\sqlalchemy\engine\base.py:2335: in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
.venv\Lib\site-packages\sqlalchemy\engine\cursor.py:1135: in fetchall
    rows = dbapi_cursor.fetchall()
.venv\Lib\site-packages\oracledb\cursor.py:828: in fetchall
    row = fetch_next_row(self)
src\\oracledb\\impl/base/cursor.pyx:441: in oracledb.base_impl.BaseCursorImpl.fetch_next_row
    ???
src\\oracledb\\impl/thick/cursor.pyx:151: in oracledb.thick_impl.ThickCursorImpl._fetch_rows
    ???
src\\oracledb\\impl/thick/utils.pyx:428: in oracledb.thick_impl._raise_from_odpi
    ???
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

>   ???
E   sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-01805: possible error in date/time operation
E   (Background on this error at: https://sqlalche.me/e/20/4xp6)

src\\oracledb\\impl/thick/utils.pyx:418: DatabaseError
===================================================== short test summary info ======================================================
FAILED tests/integration/test_api/test_ppts.py::test_get_all_ppts - sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-01805: possible error in date/time operation
======================================================== 1 failed in 54.05s ========================================================

[anthony-tuininga]

The error ORA-01805: possible error in date/time operation generally implies that you have a different client version than the database version. If you make them match the issue should go away. As noted you can also avoid using named time zones (as in alter session set time_zone = 'America/Edmonton') and instead use numeric time zone offsets (as in alter session set time_zone = '-06:00').

[nmoreaud]

As a workaround, what can I use to transform a timestamp to UTC? Are these query equivalent?

select cast(mytimestamp as timestamp) at time zone 'UTC';
select cast(mytimestamp as timestamp) at time zone '+00:00';

[anthony-tuininga]

They should indeed be equivalent.

[ggasnier72]

We are using _currenttimestamp to update timestamp with time zone column. The trigger is defined on the table A where the timestamp with time zone column is available. With updates on some fields, the trigger is called and the column is updated with _currenttimestamp. The driver gets the value back with this format: 'DD-MM-YY HH24:MI:SSXFF TZH:TZM'

Trigger's code:

CREATE OR REPLACE TRIGGER timestamp_trigger_tbl_a BEFORE UPDATE ON TABLE_A FOR EACH ROW 
BEGIN 
   IF(:new.FIELD_A != :old.FIELD_A OR :new.FIELD_B != :old.FIELD_B OR :new.FIELD_C != :old.FIELD_C)) THEN
      :new.MODIFIED_TIMESTAMP := current_timestamp; 
   END IF; 
END;

Triggers are also defined on other tables. Updates on some fields update the column of the table A. Trigger's code:

CREATE OR REPLACE TRIGGER timestamp_trigger_tbl_b BEFORE UPDATE ON TABLE_B FOR EACH ROW 
DECLARE 
   curTime TIMESTAMP WITH TIME ZONE;
   curTimeChar VARCHAR(100);
BEGIN 
   IF(:new.FIELD_A != :old.FIELD_A OR :new.FIELD_B != :old.FIELD_B OR :new.FIELD_C != :old.FIELD_C)) THEN
      /* Explicit timestamp with time format */
      curTimeChar := TO_CHAR(current_timestamp,'DD-MM-YY HH24:MI:SSXFF TZH:TZM');
      curTime := TO_TIMESTAMP_TZ(curTimeChar,'DD-MM-YY HH24:MI:SSXFF TZH:TZM');
      /* Doesn't work */
      /* curTime := current_timestamp */
      UPDATE TABLE_A SET MODIFIED_TIMESTAMP = curTime WHERE TABLE_B_ID = :old.RECORD_ID; 
   END IF; 
END;

Without the explicit conversion, the value in the column is not correct. Its format is 'DD-MM-YY HH24:MI:SSXFF TZR'. And the driver can't get the value back because of the exception: DPY-3022: named time zones are not supported in thin mode It seems that in the second trigger, if we use _curTime := currenttimestamp, the database NLS_TIMESTAMP_TZ_FORMAT parameter is used.

Do you think there are other alternatives to get the correct format ? Do you have a roadmap that includes timezone support ?

[nmoreaud]

@ggasnier72 presents a use case we are working on. We want to store a "last modified date" (UTC) information on a root object (ex "order"). Each update on a sub-object (ex order_product, delivery, parcel, address) must also update the root object "last modified date", via a trigger. We have implemented it successfully for multiple database providers, but we have difficulties with Oracle because of the driver has date support limitations:

• 20

• 274

We are looking for a solution which will not regress with a future update of OracleDB driver. Maybe a discussion would be more appropriate.