python: oracledb ignores position of positional bind valuedoes not bind positional values correctly

Question

i want to update a table in oracle using python3 with oracledb and positional values.

A simple example, which is not working:

sql = "update table set col1 = :2, col2 = :3 where id = :1" data = (101, "Hello", "World") cur.execute(sql, data)

This just won't work and oracle reports invalid number. Changing the data to ("Hello", "World", 101) works just fine.

The documentation clearly states this should work, there is even an example for this with an insert: https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#bind-by-position

cursor.execute(""" insert into departments (department_id, department_name) values (:1, :2)""", (280, "Facility"))

I hope ther is a better solution than to reposition the data every time.

Answer 1

"Positional" means that the names of the parameters (:2, :3, :1 here) are ignored and instead their position in the SQL text matters. Therefore, :2 will receive the first value in the list (because it is the first parameter in the text), :3 the second one and finally :1 the last one.

As your linked documentation says: The following example (which changes the order of the bind placeholder names) has exactly the same behavior.