update query with cursor.executemany() gives 0 rows updated, DPY-4009 or DPY-2010

[urosdigital]

Hi,

I have a list of lists to use for update query ->
chunk = [['202211231855235525', '228024357162572'], ['202209131156285076', '228023553168747'], ['202101211532410542', '228024356695672']]

Note the elements are the same type (str).
The following code is executed with no errors but there are 0 rows updated. Expected 3 rows to be updated.

query = '''update table set colx = :2 where coly = :1'''
cursor.executemany(query, chunk)
updated_rows = cursor.rowcount
print(updated_rows)

If i put brackets around chunk variable like shown below:
cursor.executemany(query, [chunk])

the following error is displayed:
DPY-4009: 2 positional bind values are required but 3 were provided

On the other hand, in case i use mixed types of data in the list of lists where first element in int64 and the second is string as shown below:
chunk = [[202211231855235525, '228024357162572'], [202209131156285076, '228023553168747'], [202101211532410542, '228024356695672']]
and use the following code (not using brackets around chunk variable)

query = '''update table set colx = :2 where coly = :1'''
cursor.executemany(query, chunk)
updated_rows = cursor.rowcount
print(updated_rows)

The code is executed without errors and 0 rows are updated.

In the end when i wrap chunk variable with brackets and use mixed types of data inside list (int64, str)
chunk = [[202211231855235525, '228024357162572'], [202209131156285076, '228023553168747'], [202101211532410542, '228024356695672']]
query = '''update table set colx = :2 where coly = :1'''
cursor.executemany(query, [chunk])
updated_rows = cursor.rowcount
print(updated_rows)

I get
DPY-2010: element 228024357162572 is not the same data type as previous elements.

Can someone help with this one? is it possible that list of list causing this issue? Here is suggested to use list of tuples -> https://cx-oracle.readthedocs.io/en/latest/user_guide/batch_statement.html

BR,
Uros

[anthony-tuininga]

There may be an issue in how you perceive bind by position works. In your case the value of ":2" will be the first element of your array and ":1" will be the second element of your array. Bind by position works from left to right and pays no attention to the name of the bind variable.

If I create a table and populate it with this code:

create table disc_126 (
    colx varchar2(50) not null,
    coly varchar2(50) not null
);

insert into disc_126 values ('UNSET', '228024357162572');
insert into disc_126 values ('UNSET', '228023553168747');
insert into disc_126 values ('UNSET', '228024356695672');

commit;

Then this code:

chunk = [
    ['202211231855235525', '228024357162572'],
    ['202209131156285076', '228023553168747'],
    ['202101211532410542', '228024356695672']
]

query = "update disc_126 set colx = :2 where coly = :1"
cursor.executemany(query, chunk, arraydmlrowcounts=True)
print("row count:", cursor.rowcount)
print("dml row counts:", cursor.getarraydmlrowcounts())

displays the following output:

row count: 3
dml row counts: [1, 1, 1]

Note the use of the array DML row counts feature. This will return an array of the number of rows updated for each element of the input array. This is optional, but can be useful in some situations, so I thought I would include it.

[urosdigital]

Hi @anthony-tuininga ,

Yes! That was the root cause. I guess i have never used "mixed" positional bindings in my scripts before. :)

Thanks for a prompt feedback! :)

BR,
Uros