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?

Christopher Jones · Accepted Answer · 2025-02-24 21:01:08Z

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.

answered Feb 24 at 21:01

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

bilak Feb 25 at 7:53

great thick mode is exactly how it worked before. There is already instant client installed. I know about the issues with bind by position, but that's a proprietary application which is going to be replaced, so I just want to keep it alive until then.

Barmar · Accepted Answer · 2025-02-24 21:18:05Z

0

I think the problem may be due to using :1 twice in the query.

You don't need E_ID = :1 in the SET clause, since you're setting the column to the same value it already has (because of WHERE E_ID = :1). So remove that:

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

edited Feb 24 at 21:18

answered Feb 24 at 16:52

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

bilak Feb 25 at 7:27

I know and I wondered why it's there, but this is some proprietary application with a lot of code and rewriting all of those selects at the moment is not the case

Collectives™ on Stack Overflow

bind parameters by array index

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related