Not able to to read '01-JAN-4712 00.00.00' date value

[sam03489]

1. What versions are you using?

oracledb.version: 2.2.1

platform.platform: Linux-4.18.0-513.24.1.el8_9.x86_64-x86_64-with-glibc2.35
sys.maxsize > 2**32: True
platform.python_version: 3.10.12

Oracle DB version: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

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

it is an error.

Unable to fetch date column with value '01-JAN-4712 00.00.00'

Error I get is ValueError: year 60824 is out of range

SELECT EARLIEST_SHIP_DATE
FROM apps.OE_ORDER_LINES_ALL where line_id=1096

If I use the below SQL, it works.
SELECT TO_DATE(to_char(EARLIEST_SHIP_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')
FROM apps.OE_ORDER_LINES_ALL where line_id=1096

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

Unable to fetch date column with value '01-JAN-4712 00.00.00'
Error I get is ValueError: year 60824 is out of range

SELECT EARLIEST_SHIP_DATE
FROM apps.OE_ORDER_LINES_ALL where line_id=1096

4. Does your application call init_oracle_client()?

No. I am using Thin mode

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

Code which does not work:-

import oracledb
conn = oracledb.connect(**conn_dict)
query = """
SELECT EARLIEST_SHIP_DATE FROM apps.OE_ORDER_LINES_ALL where line_id=1096
"""
cur = conn.cursor()
cur.execute(query)
rs = cur.fetchone()
print(rs)
cur.close()
conn.close()

Code which works:-

import oracledb
conn = oracledb.connect(**conn_dict)
query = """
SELECT TO_DATE(to_char(EARLIEST_SHIP_DATE, 'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') EARLIEST_SHIP_DATE
FROM apps.OE_ORDER_LINES_ALL where line_id=1096
"""
cur = conn.cursor()
cur.execute(query)
rs = cur.fetchone()
print(rs)
cur.close()
conn.close()

[anthony-tuininga]

I strongly suspect this is because of corrupted data stored in your database. Try this:

import oracledb

conn = oracledb.connect("user/password@host:port/service_name")
cursor = conn.cursor()
cursor.execute("""
    select
        to_date('4712-01-01', 'YYYY-MM-DD'),
        dump(to_date('4712-01-01', 'YYYY-MM-DD'))
    from dual""")
for row in cursor:
    print(row)

That works just fine for me and I suspect it will also work just fine for you. This is my output:

(datetime.datetime(4712, 1, 1, 0, 0), 'Typ=13 Len=8: 104,18,1,1,0,0,0,0')

Run dump() on the date in your database. I suspect you will see that it is corrupted.

[sam03489]

It is a production data so do not think data is corrupted. Why will to_date(to_char()) work and without it, it will not.

I added timestamp to your code.

[sam03489]

It does look like a bug to me.

[anthony-tuininga]

The time portion defaults to 0 so adding that doesn't make any difference. :-) You can see that the output is identical.

Run dump() on the date in your database and see if it matches the information shown for the specific date. Note that some clients are able to insert dates without them being validated. The code that is performing the to_char() is not affected by the invalid data. Correcting the data in your database would be the right course of action -- once you have indeed verified that it is corrupt, of course!

[anthony-tuininga]

It does look like a bug to me.

Let's see what is actually stored in the database, first.

[sam03489]

Here you go

[sam03489]

with cx_Oracle package, I am able to make it work using "outputtypehandler" feature but it does not work in Oracledb.
It does not even go to DateTimeConverter function. Please note that we are able to pull the data using informatica power center, which uses odbc driver. I do think it is a BUG.

import os
import cx_Oracle
import datetime

def DateTimeConverter(value):
    if value == '01-JAN-12':
        return datetime.datetime.strptime('01-01-4712', '%d-%m-%Y')
    else:
        return value

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
cur = conn.cursor()
sql = """
select
        EARLIEST_SHIP_DATE,
        dump(EARLIEST_SHIP_DATE)
    FROM apps.OE_ORDER_LINES_ALL where line_id=1096
"""
cur.execute(sql)
for row in cur:
    print(row)
    
print(cx_Oracle.version)
cur.close()
conn.close()

[anthony-tuininga]

Ok. I realized that the output didn't quite match, so I did this instead. Create a table and populate it with the hard-coded value:

create table issue_345 (
    date_val date
);

insert into issue_345 values (to_date('4712-01-01', 'YYYY-MM-DD'));

commit;

You can insert your date into that table by selecting it from your production table. Then run this Python code:

import oracledb

conn = oracledb.connect(CONNECT_STRING)
cursor = conn.cursor()
cursor.execute(
    """
    select date_val, dump(date_val)
    from issue_345
    """
)
for row in cursor:
    print(row)

This yields the following output:

(datetime.datetime(4712, 1, 1, 0, 0), 'Typ=12 Len=7: 147,112,1,1,1,1,1')

The first value should be 100 greater than the century (147 - 100 = 47) and the second value should be 100 greater than the year inside the century (112 - 100 - 12). In your date the values are 53 - 100 = -47 and 88 - 100 = -12, which is incorrect. This demonstrates that the value stored in the database has been corrupted. It should be updated to the correct value.

[sam03489]

If the value stored in the database is corrupted then how are other clients working? (Informatica extracts data using it's native ODBC driver). The fact that TO_CHAR(TO_DATE()) is working makes me believe that python code which converts date to varchar itself is not working. When I tested the code with cx_Oracle package I could see the value coming across as "01-01-12" and I think oracledb package is not able to understand it and is throwing the error.

Between data is from production EBS R12 system. I do not think data is corrupted.
This issue is specific to python client.

Please see the CX_ORACLE code snippet I shared.

[sam03489]

Our organization has license for Oracle Support. If I create a ticket with Oracle support, will it be the right approach?
Data definitely is not corrupted as there are millions of record with this value and the applications are working fine.

[anthony-tuininga]

The other clients may have worked around this data corruption and python-oracledb can do the same, if that is the case. I will check internally to see if this is expected or a common corruption which has workarounds enabled and get back to you.

Note that python-oracledb is a replacement for cx_Oracle and if you enable thick mode (by calling oracledb.init_oracle_client()) then you will use the same Oracle Client libraries that were used with cx_Oracle and you will get the same behavior as before.

Just to get some better understanding of the situation. Can you create the table I suggested earlier and populate it with the row I gave as well as the row from your table that isn't being fetched correctly? Then run dump() on both rows and also attempt to fetch from the table using this:

select date_val from issue_345
where date_val = to_date('4712-01-01', 'YYYY-MM-DD');

If you see both rows, please let me know! Thanks.