How to convert a python array into rows in Oracle

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)
)

Comments

Add Comment

Name

Email

Comment

Are you human? * eight = 16