Add support for named time zones in thin mode

Inizia dallo script Python eseguibile incluso nell’issue e confronta la query con fuso orario denominato che fallisce con le query timestamp funzionanti nella configurazione indicata di Oracle 11g e del driver. Il lavoro è completato quando il recupero di un timestamp con un fuso orario denominato restituisce valori datetime che conservano le informazioni sul fuso orario, anche per l’esempio di tabella con TIMESTAMP WITH TIME ZONE.

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