bind parameters by array index

Question

I'm trying to migrate from cx_Oracle 8.1.0 to oracledb 2.5.1 library. Before such query was working:

update command_center set 
            E_ID = :1,
            E_ASPECTS = :2,
            E_CREATED_TIMESTAMP = :3
WHERE e_id = :1

def bulk_insert_or_update_data_to_targets(self, sql, target_data, id_index=0):
    for target_connections, data in target_data:
        for con in target_connections:
            cursor = con.cursor()
            cursor.executemany(sql, data, batcherrors=True)
            if cursor.getbatcherrors():
                self.logger.debug("data: " + str(data))
            cursor.close()

but currently I have following exception:

oracledb.exceptions.DatabaseError: DPY-4009: 4 positional bind values are required but 3 were provided

Is there any chance this could work the same way it worked in cx_Oracle before ... so I guess it retrieved the attribute by array index?

Answer 1

• Enable 'thick mode', see https://python-oracledb.readthedocs.io/en/latest/user_guide/initialization.html#enabling-python-oracledb-thick-mode
• Use named bind variables, instead of bind-by-position.

The latter is preferred because it doesn't run into the kind of issue you see. Even Thick mode has some variances with bind-by-position that depend if you are using SQL or PL/SQL statements.