Closed
Description
- 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)
- 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.
- 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()