NUMTODSINTERVAL : oracledb.exceptions.DatabaseError: ORA-01873: the leading precision of the interval is too small

[indiVar0508]

Hi,

I have some query in my app that leverages NUMTODSINTERVAL to convert seconds to oracle Interval, but
getting below mentioned error, when trying to insert a datetime.timedelta interval value of more than 99 days.
But the documentation says default precision for NUMTODSINTERVAL is 9
ref : https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/NUMTODSINTERVAL.html#GUID-5A7392A8-7976-4465-8839-A65EFF1A80B6

Traceback (most recent call last):
  File "/home/user/temp3.py", line 56, in <module>
    cursor.execute(insert_value_sql)
  File "/home/user/venv_py311/lib/python3.11/site-packages/oracledb/cursor.py", line 383, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 132, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 420, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 421, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 414, in oracledb.thin_impl.Protocol._process_message
oracledb.exceptions.DatabaseError: ORA-01873: the leading precision of the interval is too small
Help: https://docs.oracle.com/error-help/db/ora-01873/

Please find below the Minimal Reproducible code for same.

import oracledb

# 2. Establish a connection to your Oracle database
connection = oracledb.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.1. 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()

could you help me resolve this,
Thanks!

[cjbj]

See comments in oracle/python-cx_Oracle#660 . Since you are passing fixed SQL statements to the database, this isn't a driver problem.