How to Resolve ORA-01036 in executemany() for Python oracledb: fix quoted placeholders and bind order

Troubleshooting ORA-01036 in executemany() for Python oracledb: see why quoted placeholders and mismatched bind order break batch updates, and apply a safe fix.

Fixing ORA-01036 in executemany(): how bind variables and column order can break batch updates

Introduction

Batch updates with executemany() in Python are supposed to be straightforward, yet a small mismatch in bind variables can trigger ORA-01036: illegal variable name/number. Curiously, the same update may succeed when executed row by row with execute(). Below is a concise walkthrough of why this happens and how to align SQL placeholders with your data in oracledb (Python 3.9, oracledb 1.4.1).

Problem demonstration

The failing path uses batch execution with positional binds. The SQL text quotes one of the placeholders and the tuple order doesn’t match the bind positions.

df_pairs = src_df[["CONTRACT_NUM", "DOCID"]].copy()
cur = dbh.cursor()
batch_vals = [tuple(x) for x in df_pairs.values]
print(batch_vals)
stmt = "UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive SET DOCID = :2    WHERE CONTRACT_NUM = ':1'"
cur.executemany(stmt, batch_vals)
rows_changed = cur.rowcount
dbh.commit()
cur.close()

DatabaseError: ORA-01036: illegal variable name/number

Why this fails

The core issue is how bind variables are used. The placeholder ':1' is quoted in the SQL, which makes it a string literal rather than a bind variable. As a result, Oracle cannot bind a value to it. Another subtlety is that numbering in placeholder names does not tie to tuple indices; the order in the SQL defines the binding by position unless you use named binds. In the broken statement, DOCID is assigned using :2 while the WHERE clause uses ':1', but the input tuples are constructed as (CONTRACT_NUM, DOCID). This mismatch between the tuple ordering and the positional placeholders creates confusion and leads to the error. In contrast, the per-row execute() path works because values are interpolated directly into the SQL text, including quotes for strings, so binds are not involved there.

Solution and corrected code

Remove quotes from bind placeholders, and make the data order match the SQL bind positions. Since the SQL assigns DOCID and filters by CONTRACT_NUM, the first value in each tuple must correspond to DOCID and the second to CONTRACT_NUM.

# Reorder the columns to match the SQL bind positions
pairs_reordered = src_df[["DOCID", "CONTRACT_NUM"]].copy()

cur2 = dbh.cursor()
bulk_params = [tuple(v) for v in pairs_reordered.values]
print(bulk_params)

sql_fixed = """
UPDATE USERBOX_MIDDLE_OFFICE.rvp_doc_partner_to_archive
SET    DOCID = :1
WHERE  CONTRACT_NUM = :2"""

cur2.executemany(sql_fixed, bulk_params)
affected_total = cur2.rowcount
dbh.commit()
cur2.close()

This aligns the first bind with DOCID and the second with CONTRACT_NUM. There is no need to wrap string values in apostrophes; the driver handles that. What matters is that you do not quote the placeholder itself, and that the columns you pass in your tuples are ordered to match the binds in the statement.

Why this matters

Understanding bind variables is essential for reliable batch operations. The distinction between binding by position and the visible order of your input data directly controls how executemany() maps values into the SQL. Knowing that numbers in placeholder names are not tied to tuple indices prevents misleading assumptions. Being aware that you should not quote bind placeholders avoids silent bugs and runtime errors.

Takeaways

If executemany() raises ORA-01036 while a per-row execute() appears to work, check two things. First, ensure placeholders are not wrapped in quotes. Second, align the column order in your data with the positional bind order in your SQL, or explicitly bind by name. With those adjustments, batch updates in Python 3.9 and oracledb 1.4.1 behave predictably and consistently.