When using time formatting in SQL with bind variables, cx_Oracle interprets colons in time format as bind variables - How to handle?

[Runbacktoo]

cx_Oracle version: 8.3.0

When executing an SQL statement that includes time formatting (HH24:MI:SS) and uses bind variables, cx_Oracle mistakenly interprets the colons in the time format as part of the bind variables, leading to errors in SQL execution. How can this issue be addressed to correctly handle time formats in SQL statements without compromising the use of bind variables?

UPDATE TAB
SET START_TIME = TO_DATE(:delay_date, 'YYYY-MM-DD HH24:MI:SS'),
    LAST_EDIT_DATE = SYSDATE,
    LAST_EDIT_BY = :name WHERE ID = :id

params = {'delay_date': '2023-01-01 12:00:00', 'name': 'E12345', 'id': '1001'}
cursor.execute(sql, params)

[anthony-tuininga]

What error are you getting? I tried this script:

import cx_Oracle as oracledb

conn = oracledb.connect("user/password@host:port/service_name")
cursor = conn.cursor()

params = {'delay_date': '2023-01-01 12:00:00', 'name': 'E12345', 'id': '1001'}
cursor.execute("""
        select
            to_date(:delay_date, 'YYYY-MM-DD HH24:MI:SS'),
            :name,
            :id
        from dual""", params)
for row in cursor:
    print(row)

and did not get any errors. I also tried it with python-oracledb, the replacement for cx_Oracle, and didn't run into any errors there, either. Can you try this script?

[Runbacktoo]

What error are you getting? I tried this script:

import cx_Oracle as oracledb

conn = oracledb.connect("user/password@host:port/service_name")
cursor = conn.cursor()

params = {'delay_date': '2023-01-01 12:00:00', 'name': 'E12345', 'id': '1001'}
cursor.execute("""
        select
            to_date(:delay_date, 'YYYY-MM-DD HH24:MI:SS'),
            :name,
            :id
        from dual""", params)
for row in cursor:
    print(row)

and did not get any errors. I also tried it with python-oracledb, the replacement for cx_Oracle, and didn't run into any errors there, either. Can you try this script?

I apologize, it might have been due to my spelling mistake. In subsequent usage, I indeed haven't encountered a similar situation again.