Arrays as bind variables in queries?

[Aetylus]

Are there any plans to allow a parameter to contain multiple values for use in a query (such as with an in operator)? e.g.

query_statement = '''
    select *
    from employees
    where id in (:ids)
'''

cursor = connection.cursor()
cursor.execute(query_statement, ids=[1, 2])

At the moment, I've at least come up with a rough temporary solution by subclassing Cursor:

import collections
import cx_oracle as db
import re
from copy import copy, deepcopy

class connection(db.connection):
    def cursor(self):
        return cursor(self)

class cursor(db.cursor):
    def _expand_sequence_arg(self, arg):
        expanded_arg = {}
        for i, element in enumerate(arg):
            expanded_arg[f'{element}_{i + 1}'] = element
        return expanded_arg

    def execute(self, statement, *args, **kwargs):
        arguments = deepcopy(args)
        keyword_arguments = dict(kwargs)
        print(arguments)

        if args:
            if isinstance(args[0], tuple) or isinstance(args[0], list):
                for i, arg in enumerate(args[0]):
                    if isinstance(arg, collections.sequence) and not isinstance(arg, str):
                        statement = re.sub(r':\w+', ', '.join(f':{i}' for i in range(1, len(arg) + 1)), statement, i + 1)
                        temp_args = list(args[0])
                        temp_args.pop(i)
                        temp_args[i:i] = arg
                        arguments = (tuple(temp_args),)
            elif isinstance(args[0], dict):
                for key, value in args[0].items():
                    if isinstance(value, collections.sequence) and not isinstance(value, str):
                        expanded_arg = {}
                        for i, element in enumerate(value):
                            expanded_arg[f'{key}_{i + 1}'] = element
                        statement = statement.replace(f':{key}', ', '.join(f':{key}' for key in expanded_arg.keys()))
                        arguments[0].pop(key)
                        arguments[0].update(expanded_arg)
        elif kwargs:
            for key, value in kwargs.items():
                if isinstance(value, collections.sequence) and not isinstance(value, str):
                    expanded_arg = {}
                    for i, element in enumerate(value):
                        expanded_arg[f'{key}_{i + 1}'] = element
                    statement = statement.replace(f':{key}', ', '.join(f':{key}' for key in expanded_arg.keys()))
                    keyword_arguments.pop(key)
                    keyword_arguments.update(expanded_arg)
        return super().execute(statement, *arguments, **keyword_arguments)

However, this makes the assumption of that there are no other bind variables named in the expanded format. i.e. You can't have a bind variable :foo and :foo_1 within the query. There are probably other issues as well that I haven't accounted for, and all this could probably be better handled in the native C sections of the code.

Alternatively, is there a better method of handling this other than manipulating the query string? I thought it might be possible to have executemany run a select statement, but any attempts to do so returns back an error:

cx_Oracle.DatabaseError: DPI-1013: not supported

Answer the following questions:

1. What is your version of Python? Is it 32-bit or 64-bit?
   64-bit

2. What is your cx_Oracle version?
   7.0

3. What is your Oracle Database version?
   12c 12.1.0.2.0

[cjbj]

This is a SQL 'feature' and not something cx_Oracle controls or will change. See Binding Multiple Values to a SQL WHERE IN Clause.

[Updated with new cx_Oracle doc link]

[anthony-tuininga]

As noted by Chris, this isn't something that can be "fixed" -- other than by subclassing the cursor and adjusting the actual SQL and bind variables that are being passed as you have done. As you discovered, you cannot use executemany() with a select statement -- that isn't supported by Oracle at the moment. I am going to assume that answers your question and close the issue, but feel free to re-open if that isn't the case!

[baldegit]

As noted by Chris, this isn't something that can be "fixed" -- other than by subclassing the cursor and adjusting the actual SQL and bind variables that are being passed as you have done. As you discovered, you cannot use executemany() with a select statement -- that isn't supported by Oracle at the moment. I am going to assume that answers your question and close the issue, but feel free to re-open if that isn't the case!

is it also true on oracle-nodejs ? i have a similar problem ["ERROR: DPI-1013: not supported"].

[anthony-tuininga]

As noted by Chris, this isn't something that can be "fixed" -- other than by subclassing the cursor and adjusting the actual SQL and bind variables that are being passed as you have done. As you discovered, you cannot use executemany() with a select statement -- that isn't supported by Oracle at the moment. I am going to assume that answers your question and close the issue, but feel free to re-open if that isn't the case!

is it also true on oracle-nodejs ? i have a similar problem ["ERROR: DPI-1013: not supported"].

Yes. That is true for all drivers that use the underlying Oracle Client libraries.

[baldegit]

merci pour la réponse rapide. in node JS Works fine for me if we put a BEGIN ... END; around the query. hope this can help some one else :).