ORA-01873: the leading precision of the interval is too small

[indiVar0508]

1. What versions are you using?
   platform.platform: Linux-5.15.0-83-generic-x86_64-with-glibc2.31
   sys.maxsize > 2**32: True
   platform.python_version: 3.11.5
   cx_Oracle.version: 8.3.0
   cx_Oracle.clientversion: (21, 11, 0, 0, 0)

2. Describe the problem

NUMTODSINTERVAL doesn't seem to work properly i think.
documentation suggests it has 9 default precision but if i try to insert a value more than 99 days it fails
documentation link : https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/NUMTODSINTERVAL.html#GUID-5A7392A8-7976-4465-8839-A65EFF1A80B6
please refer to below mentioned Minimum reproducible code.

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

import cx_Oracle

# 2. Establish a connection to your Oracle database
connection = cx_Oracle.connect("SYSTEM/ORacle123@//127.0.0.1:1521/XEPDB1")

# 3. Create a cursor
cursor = connection.cursor()

# 4. Create the table with an interval column
create_table_sql = """
CREATE TABLE my_interval_table (
    my_interval_column INTERVAL DAY TO SECOND
)
"""

cursor.execute(create_table_sql)

# 5. Insert a 10 days interval value into the table
insert_value_sql = """
INSERT INTO my_interval_table (my_interval_column) VALUES (NUMTODSINTERVAL(86400.0, 'SECOND'))
"""

cursor.execute(insert_value_sql)

# 5. Insert a 100 days value into the table, fails with error
#    oracledb.exceptions.DatabaseError: ORA-01873: the leading precision of the interval is too small
insert_value_sql = """
INSERT INTO my_interval_table (my_interval_column) VALUES (NUMTODSINTERVAL(8640000.0, 'SECOND'))
"""

cursor.execute(insert_value_sql)

# Commit the changes to the database
connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()

[cjbj]

cx_Oracle is passing those SQL statements directly to the DB. In other words this is not a cx_Oracle problem.

Suggestions:

• Upgrade to Oracle Database 23c Free
• Ask on one of the forums at https://forums.oracle.com/ords/apexds/domain/dev-community
• Upgrade to python-oracledb (not that this will help, since the problem isn't related to the driver)