cur.execute('SELECT * FROM foo WHERE bar IN :baz', {'baz': [1,2,3]})

[mkmoisen]

Would you please add support for bindparameters on a collection for IN data type?

---

If I attempt to use a bindparameter in the IN clause of a select statement, for example:

values = [1,2,3]

cur.execute('''
    SELECT *
    FROM foo
    WHERE bar IN :baz
''', {
    'baz': values
})

It will fail with:

cx_Oracle.DatabaseError: ORA-01484: arrays can only be bound to PL/SQL statements

The work around to this is to write something like the following:

bind_params = ', '.join(f':bindparam{i}' for i in range(len(values))

cur.execute(f'''
    SELECT *
    FROM foo
    WHERE bar IN {bind_params}
''', {
    f'bindparam{i}': value for i, value in enumerate(values)
})

It would be nice if cx_Oracle just did that automatically.

[sharadraju]

Please check the following notebook in github: 2-Queries.ipynb This has a section on how to easily use collections as bind parameters with WHERE IN clauses

[cjbj]

This is really a lower level database design and not in our direct control. We have in the past mentioned this general requirement in some review meetings but I am not expecting any change (so I'll close this issue). Currently, as you found, each data value has to be represented by one bind placeholder in the SQL string. Yes the driver could do some internal manipulation of your SQL statement and add placeholders for each list entry before sending it to the DB, but that would be non-Oracle standard, have its own issues, and your app code can do exactly the same now. We're not planning on doing this in the driver.

@sharadraju linked to some WHERE IN examples which are also visible in the documentation Binding Multiple Values to a SQL WHERE IN Clause. You can bind a list if you use it to build an object which is then treated as a single bind. But for performance reasons most people will not want to. The documentation could be strengthened with some more warnings against doing it as a general solution because object manipulation requires a number of round trips and are slow.