Unable to Read Dates with Year -4712

[iamlorax]

1. What versions are you using?

oracledb.version: 2.4.1

platform.platform: Windows-10-10.0.17763-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.11.9

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

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

Error

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

$ python test.py
Traceback (most recent call last):
File "E:\zzzz\test.py", line 14, in
print(result.fetchall())
^^^^^^^^^^^^^^^^^
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\result.py", line 1317, in fetchall
return self._allrows()
^^^^^^^^^^^^^^^
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\result.py", line 548, in _allrows
rows = self._fetchall_impl()
^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\cursor.py", line 2135, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\cursor.py", line 1140, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\cursor.py", line 1081, in handle_exception
result.connection._handle_dbapi_exception(
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\base.py", line 2358, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2])
File "C:\Users\zzzz\AppData\Local\Programs\Python\Python311\Lib\site-packages\sqlalchemy\engine\cursor.py", line 1136, in fetchall
rows = dbapi_cursor.fetchall()
^^^^^^^^^^^^^^^^^^^^^^^
File "C:\Users\wegscha\AppData\Local\Programs\Python\Python311\Lib\site-packages\oracledb\cursor.py", line 779, in fetchall
row = fetch_next_row(self)
^^^^^^^^^^^^^^^^^^^^
File "src\oracledb\impl/base/cursor.pyx", line 548, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
File "src\oracledb\impl/base/cursor.pyx", line 260, in oracledb.base_impl.BaseCursorImpl._create_row
File "src\oracledb\impl/thick/var.pyx", line 189, in oracledb.thick_impl.ThickVarImpl._get_scalar_value
File "src\oracledb\impl/thick/var.pyx", line 365, in oracledb.thick_impl.ThickVarImpl._transform_element_to_python
File "src\oracledb\impl/thick/utils.pyx", line 336, in oracledb.thick_impl._convert_to_python
File "datetime.pxd", line 298, in cpython.datetime.datetime_new
ValueError: year -4712 is out of range

4. Does your application call init_oracle_client()?

Yes.

    sqlalchemy.create_engine(self.sqlalchemy_url, thick_mode=True)

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

import oracledb
import sqlalchemy
from sqlalchemy import text

sqlalchemy_url = f'oracle+oracledb://{config["username"]}:{config["password"]}@{config["host"]}:{config["port"]}/?service_name={config["service_name"]}'
engine = sqlalchemy.create_engine(sqlalchemy_url, thick_mode=True)

query = text("""
SELECT TO_DATE('-4712-01-01','SYYYY-MM-DD') FROM DUAL
""")

with engine.connect() as connection:
    result = connection.execute(query)
    print(result.fetchall())

[cjbj]

This is expected. From #345 (comment):

Python doesn't handle this value

Also see the test https://github.com/oracle/python-oracledb/blob/v2.4.1/tests/test_1400_datetime_var.py#L300-L306

[iamlorax]

Ah, my bad. I saw that issue and the final comment was that the "issue was resolved" but I must have misinterpreted the context.

Given this is a legitimate Oracle value, I presume there is a way to ingest this value via the package. Would you be able to point me towards that? Our use case involves copying data from hundreds of Oracle source tables with queries that are auto generated (select * from xyz) so adding a clause to the queries themselves would cause a lot of overhead.

[cjbj]

The solutions in #345 (comment) are still valid.

I'll leave this open to track the request to review whether it is feasible to have some kind of OracleDate class.

[iamlorax]

Unfortunately, for our use case the provided options are not valid, however, you have shown this is not a problem with this package. I appreciate you reviewing the feasibility of an OracleDate class.