If you have a list of values that you would like to convert into a set of rows in Oracle, you can use the table function with sys.odcivarchar2list for strings, or sys.odcinumberlist for numbers.
values = ['foo', 'bar']
params = ', '.join(
[f':param{index}' for index in range(len(values))]
)
result = cur.execute(
f'''
SELECT column_value
FROM TABLE(
sys.odcivarchar2list(
{params}
)
)
''',
{
f'param{index}': value
for index, value in enumerate(values)
}
)
print(result.fetchall())
This will output:
>>> print(result.fetchall())
[('foo',), ('bar',)]
In Oracle, there is no convenient way to just pass in the array directly as a bind parameter. This will not work:
FROM TABLE(
sys.odcivarchar2list(:values)
)