Problem empty date cause ValueError: year -9999 is out of range

[doudz]

I'm using cx_Oracle 7.2.2 with Oracle instant client 11.2.0.4 on a Oracle 10g database.
I'm running Ubuntu 18.04 LTS with Python 3.6 (tried Python 3.7 with the same result)
Everything is fine except when I tried to retrieve empty date

Example using sqlplus :

SQL> SELECT T$EMNO,T$EDTE,to_char(T$EDTE,'YYYY-MM-DD') FROM BAANDB.TTCCOM001300 WHERE T$EMNO=255;

    T$EMNO T$EDTE   TO_CHAR(T$EDTE,'YYYY-MM-DD')
---------- -------- ------------------------------
       255 01/01/99 0000-00-00

Same example using cx_Oracle

In [7]: cursor.execute("SELECT T$EMNO,T$EDTE,to_char(T$EDTE,'YYYY-MM-DD') FROM BAANDB.TTCCOM001300 WHERE T$EMNO=255")
Out[7]: <cx_Oracle.Cursor on <cx_Oracle.Connection to baandb@tbaan>>

In [8]: cursor.fetchone()
---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-8-5bbb96413e0a> in <module>()
----> 1 cursor.fetchone()

ValueError: year -9999 is out of range

On the same database, if I use cx_Oracle 5.2.1 and Oracle Instant client 10.2 with older python 3.5 it was working but I think it was a python bug as said here #166

Is there a way to configure cx_Oracle or instant client to convert such date to None or other working value ?
I can't upgrade the database itself because it's used by an ERP.

[doudz]

is it possible to use outputtypehandler ?

[doudz]

I managed to use outputtypehandler like this

import os
import cx_Oracle
import datetime
os.environ['NLS_DATE_FORMAT'] = 'YYYY-MM-DD HH24:MI:SS'

conn = cx_Oracle.connect('user', 'pwd', 'dbase')


def DateTimeConverter(value):
    if value.startswith('9999'):
        return None
    return datetime.datetime.strptime(value, '%Y-%m-%d %H:%M:%S')


def OutputHandler(cursor, name, defaulttype, length, precision, scale):
    if defaulttype == cx_Oracle.DATETIME:
        return cursor.var(cx_Oracle.STRING, arraysize=cursor.arraysize,
                          outconverter=DateTimeConverter)


conn.outputtypehandler = OutputHandler
cursor = conn.cursor()

cursor.execute("SELECT T$EMNO,T$EDTE,to_char(T$EDTE,'YYYY-MM-DD') FROM BAANDB.TTCCOM001300 WHERE T$EMNO=255")
a = cursor.fetchone()
print(a)

(255, None, '0000-00-00')

Is it the right way or is there another solution ?
Since I need to use django, I have to customize django to add the outputtypehandler

[anthony-tuininga]

Can you provide a set of create table and insert statements that create the data you are trying to fetch? Or is the data invalid -- and it sure looks that way since a month and day of 0 are invalid! If that is indeed the case, I'd suggest updating the data to valid data (such as null) -- or, if you can't do that, creating a view that transforms the invalid date value to a null value and query from that instead of the base table.

[doudz]

I can't change values in database and I can't create a view, because I don't have access, it's managed by the ERP.
Since this database exists since 1999 I suppose the ERP use an old style "empty" value by filling the field with some kind of invalid value

[anthony-tuininga]

You can also modify the query that you are executing to do something like this:

cursor.execute("""
        select case when to_char(T$EDTE, 'YYYY-MM-DD') = '0000-00-00' then null else T$EDTE end
        from BAANDB.TTCCOM00130
        where T$EMNO=:emno""", emno=255)

If that option isn't acceptable to you, the output type handler is going to be your best bet.

[doudz]

Thanks for all
Solved using outputtypehandler