Is it possible bind variables by name AND position in Oracle SQL?

Question

When executing an Oracle SQL query from an application using a driver like python-oracledb, it is possible to bind variables by name or by position. Binding variables by name uses named arguments, and binding variables by position uses arguments given as lists or tuples.

By name:

cursor.execute('''
    SELECT 'Var values are ' || :var || ' and ' || :var AS var_value
    FROM dual
''', var="something")

The value of var_value will be 'Var values are something and something'.

By position:

cursor.execute('''
    SELECT 'Var values are ' || :var || ' and ' || :var AS var_value
    FROM dual
''', ["one thing", "another thing"])

Now the value of var_value will be 'Var values are one thing and another thing'.

Is it possible to do both? e.g.,

cursor.execute('''
    SELECT 'Var values are ' || :name_var || ', ' || :pos_var || ', and ' || :pos_var AS var_value
    FROM dual
''', ["another thing", "something else"], name_var="one thing")

I checked the documentation, but no example is given of binding by both name and position.

Answer 1

It is not possible to bind both by position and by name. If you try that you will get this error: DPY-2006: positional and named binds cannot be intermixed or DPY-2005: expecting positional arguments or keyword arguments, not both, depending on which approach you take.

Answer 2

I tried it and received this error message: "oracledb.exceptions.ProgrammingError: DPY-2005: expecting positional arguments or keyword arguments, not both".

In my particular case, I need to reference the keyword argument once before and after the positional arguments. I tested this primitive workaround, and it works:

name_var = "one thing"
pos_vars = ["another thing", "something else"]
cursor.execute('''
    SELECT
        :name_var AS first_name_ref,
        :pos_var || ' and ' || :pos_var AS pos_refs,
        :name_var AS second_name_ref
    FROM dual
''', [name_var, *pos_vars, name_var])