Oracle.ProgrammingError: positional and named binds cannot be intermixed (==6.4)

[jakereps]

Discovered in unit testing due to no version pinning in the project's Pipfile. Locally (6.3.1) remains functional, while all database set up tasks fail in our CI pipeline on cx_Oracle==6.4.

Binding errors stating that positional and named binds cannot be intermixed when there are no positional binds being used.

Answer the following questions:

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

$ python -V
Python 3.6.5
$ python -c 'import sys; print(sys.maxsize)'
9223372036854775807  # 64bit

2. What is your version of cx_Oracle?

Locally:

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.3.1

Remote (issue causing)

$ python -c 'import cx_Oracle; print(cx_Oracle.__version__)'
6.4

3. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where is it installed?

$ ls /opt/oracle/
instantclient_12_2

4. What is your version of the Oracle Database?

$ docker ps
CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS                    PORTS                                 NAMES
ffe765d011a3        store/oracle/database-enterprise:12.2.0.1-slim   "/bin/sh -c '/bin/ba…"   29 minutes ago      Up 29 minutes (healthy)   5500/tcp, 127.0.0.1:XXXXX->1521/tcp   inspiring_shtern

5. What is your OS and version?

$ uname -a
Darwin Jordens-MacBook-Pro.local 17.6.0 Darwin Kernel Version 17.6.0: Tue May  8 15:22:16 PDT 2018; root:xnu-4570.61.1~1/RELEASE_X86_64 x86_64
$ system_profiler SPSoftwareDataType
Software:

    System Software Overview:

      System Version: macOS 10.13.5 (17F77)
      Kernel Version: Darwin 17.6.0
      Boot Volume: Macintosh HD
      Boot Mode: Normal
      Computer Name: Jorden’s MacBook Pro
      User Name: Jorden Kreps (jordenkreps)
      Secure Virtual Memory: Enabled
      System Integrity Protection: Enabled
      Time since boot: 6 days 1:57

6. What compiler version did you use? For example, with GCC, run gcc --version.

$ gcc --version
Configured with: --prefix=/Library/Developer/CommandLineTools/usr --with-gxx-include-dir=/usr/include/c++/4.2.1
Apple LLVM version 9.1.0 (clang-902.0.39.2)
Target: x86_64-apple-darwin17.6.0
Thread model: posix
InstalledDir: /Library/Developer/CommandLineTools/usr/bin

7. What environment variables did you set? How exactly did you set them?

N/A

8. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?

$ pytest --cov <package_name>

9. What error(s) you are seeing?

6.3.1

...
<package_name>/tests/test_util.py ...............                            [100%]
...

6.4

...
<package_name>/tests/test_util.py EEEEEE.........                            [100%]
...

self = <sqlalchemy.dialects.oracle.cx_oracle.OracleDialect_cx_oracle object at 0x7f977ca76550>
cursor = <cx_Oracle.Cursor on <cx_Oracle.Connection to SYSTEM@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=XXXXX))(CONNECT_DATA=(SID=ORCLCDB)(SERVICE_NAME=ORCLPDB1.localdomain)))>>
statement = 'SELECT table_name FROM all_tables WHERE table_name = :name AND owner = :schema_name'
parameters = {'name': 'MYTABLE', 'schema_name': 'MYUSER'}
context = <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle object at 0x7f977cb570b8>

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       cx_Oracle.ProgrammingError: positional and named binds cannot be intermixed

Pinning to 6.3.1 resolves the issue.

[anthony-tuininga]

Can you provide a test case that fails? Or provide instructions on getting the unit tests you are referencing up and running so I can see what is going on? Thanks!

[jakereps]

For sure, it happens in the setUp portion of the unittest.TestCase, where we are making and destroying tables before and after every test so the data remains at the initial state for each test. Since the tests are erring out at the set up stage, the test code itself isn't even being ran.

Here is essentially our setUp code that recreates the error on cx_Oracle==6.4, but works as expected on ==6.3.1.

import sqlalchemy
import cx_Oracle
from sqlalchemy import Table, Column, MetaData, String

user = "SYSTEM"
password = "Oradoc_db1"

dsn = cx_Oracle.makedsn("127.0.0.1", <DOCKER_ASSIGNED_PORT>, "ORCLCDB", 
                        service_name="ORCLPDB1.localdomain")
connection_str = f"oracle+cx_oracle://{user}:{password}@{dsn}"
engine = sqlalchemy.create_engine(connection_str)

md = MetaData(bind=engine)

schema_name = "sone"
table_name = "table1"

engine.execute(f"CREATE USER {schema_name} IDENTIFIED BY test")
engine.execute(f"ALTER USER {schema_name} QUOTA UNLIMITED ON USERS")

columns = []
for column in ('foo', 'bar', 'baz'):
    columns.append(Column(column.upper(), String(50), nullable=True, 
                          quote=False))

_ = Table(table_name, md, *columns, schema=schema_name, quote=False, 
          quote_schema=False)

md.create_all()

[anthony-tuininga]

Thanks for the test case. That was sufficient to track this issue down. Essentially a bug fix in cx_Oracle 6.4 inadvertently introduced this issue. Calling cursor.setinputsizes() with no parameters at all would make the assumption that you were binding by position, so a subsequent call to cursor.execute() with named arguments would immediately raise this issue. Although it makes no sense to call cursor.setinputsizes() with no parameters, it also shouldn't cause any problems! I'll get this corrected.

[anthony-tuininga]

I have corrected this issue and added a test case that should prevent this issue from happening in the future. I'll wait a while before releasing cx_Oracle 6.4.1, just to ensure there aren't any other issues. Thanks again for reporting this issue.

[jakereps]

Awesome, thanks! No problem on holding out a hot fix, as we’ll just keep an eye out and unpin the version at that time!

[anthony-tuininga]

Re-opening as requested. Will close again once 6.4.1 has been released.

[zzzeek]

yep...i'm getting this too :) can you refer to the patch, and/or did you add tests for this?

[zzzeek]

oh sorry i see it above

[vinayinfo]

I am getting the Same error. to fix that I have downgraded to v6.3.1

[pgacv2]

Confirmed, downgrading to 6.3.1 avoids that error.

[mkai]

I have the same problem – I think it affects everyone using SQLAlchemy. Waiting to upgrade until 6.4.1 is released.

[anthony-tuininga]

cx_Oracle 6.4.1 has been released with this issue corrected in it.

[felixxm]

Many thanks Anthony :rocket:

[mPyth]

hi, I've got the same error with cx-Oracle==6.4.1, but with downgrade to version 6.3.1 everything worked nice (I've did downgrade three times to avoid mistakenly reported problem). Problem is reproduced on CentOS (centos-release-7-5.1804.el7.centos.2.x86_64), with Python 3.6.3 which runs in virtual environment. The rest of configuration: Apache with mod_wsgi and Django 2.0.7; Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.

Query is quite long, so I put it at the post end. Here is the way how query is called:

    with connections['ed'].cursor() as cursor:
        cursor.execute(query, {'p_id_ptr': 12345})

Below is a query. Please give me further instructions which are needed for bug catching (for example a kind of multi-step query bisection, to remove all comments, ...). Remark: named query param p_id_ptr is used on 7 places inside the query.

[anthony-tuininga]

Not sure what you are asking exactly. The issue on which you are commenting was corrected in cx_Oracle 6.4.1. If you have a new issue, please open a new issue and carefully explain what you are asking. Thanks.

[mPyth]

Ok, thx for prompt answer.

[hmkaraca]

We have the same with version cx_oracle 7.0 which we didn' t have with version 6.4. Can you please check and advice?

[cjbj]

@hmkaraca please give a (runnable) python script and sql to create any data. Thanks.

[hmkaraca]

Please find document attached. Code works with version 6.2.1 properly, whereas it fails with above error with 7.0. Thanks. docs.zip

[hmkaraca]

Is there any update on this?

[cjbj]

@hmkaraca please simplify your scripts to a minimal test case with logical code and as few external dependencies as possible. Make sure all necessary SQL is provided (what is LS_ANR_3G_OPTIMIZER_SETTINGS?)

[hmkaraca]

code.zip Please find code attached. I tried to minimize and modified sql, like below. args={} args['EXECUTIONGUID']='B3B6A70F330CA04CA1609D7AC8FD1FC0' sql="""SELECT \'EXECUTIONGUID\' FROM DUAL"""

[hmkaraca]

Is this information enough to check?

[cjbj]

How do we run it, where do we set the password, does it need all those imports, what's the different between the two files? I could spend time on it, or I could be on vacation :) Here's an example of a simple testcase: https://github.com/oracle/python-cx_Oracle/issues/250

[hmkaraca]

Sorry, I converted it to a single file as attached. You can run just as python test_db_con.py. Thanks. test_db_con.zip

[anthony-tuininga]

Ok. That does indeed replicate the issue. The problem is that you are calling cursor.setinputsizes() with an empty dictionary and the code looks for the presence of at least one keyword argument before considering the method to be "bind by name". So the code is assuming that you are binding by position since you didn't actually provide any keyword arguments. There isn't much point in calling setinputsizes() in this case...although I will grant you that the error message is a bit confusing!

As an aside, you aren't actually using bind variables at all but simply doing a search/replace. What you should have instead is something like this:

cursor.execute("select :execution_guid from dual",
        execution_guid="B3B6A70F330CA04CA1609D7AC8FD1FC0")

That's far simpler! You should only use setinputsizes() if you need to deviate from the default bind types.

[hmkaraca]

Actually, this was the simplest case to reproduce the problem, however, even if I use setinputsizes without empty dictionary, we have the same problem, that' s why I submitted this. I don' t have the problem with same code using version cx_oracle 6.2.1

[anthony-tuininga]

I corrected the issue with calling cursor.setinputsizes() with an empty dict. If you are having problems in other situations, please provide a new test case -- and open a new issue! Thanks.