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');