strange issue with boolean values being coerced to integer or not based on version of instantclient in use

[zzzeek]

So I'm not sure how the presence of "instantclient" affects how cx_Oracle 6.x works , since I use Oracle 6.x here without any kind of separate client library. Have a user who gets the error with this script (and I do not). I would gather that using setinputsizes() would fix, however we're trying to avoid having to use that these days:

import cx_Oracle

c = cx_Oracle.connect(
    dsn=cx_Oracle.makedsn("oracle1120", 1521), user="scott", password="tiger")

cursor = c.cursor()

try:
    cursor.execute("drop table foo")
except:
    pass

cursor.execute("""
    create table foo(
        id integer primary key,
        data SMALLINT
    )
""")

cursor.execute("INSERT INTO foo (id, data) VALUES (:id, :data)", {"id": 1, "data": True})
cursor.execute("UPDATE foo SET data=:data", {"data": False})

I don't use instantclient, so it passes for me, sending True/False constants for the integer value without calling setinputsizes(). However per the user, with oracle instantclient 12.2.0.1.0 they get this trace:

Traceback (most recent call last):
  File "test_michael2.py", line 20, in <module>
    cursor.execute("INSERT INTO foo (id, data) VALUES (:id, :data)", {"id": 1, "data": True})
cx_Oracle.DatabaseError: ORA-03115: unsupported network datatype or representation

with oracle instantclient 11.2, there's no error.

Need to understand what's going on here, and how exactly True/False, which are themselves integers, are not going in correctly (what could they be going in as?)

[anthony-tuininga]

This happens when you have an Oracle Client version 12.2 accessing an Oracle 12.1 version database. If you change the client to 12.1 (or 11.2) then all works as expected. Or you can upgrade the backend to 12.2. This is (unfortunately) a known issue.

Looking at this, though, true booleans are only really supported in PL/SQL, not in plain SQL as is being used in the above example. If you're never using PL/SQL then this can be converted to whatever makes sense to you. Some people use integers (0 or 1) and some use characters (Y or N) to denote "booleans" in Oracle so its not obvious which one is desired, unfortunately!

[zzzeek]

True/False in Python are integers. What is cx_Oracle telling the oracle client library in the case of True/False ? can't it just send a 1/0 like most other DBAPIs ?

[zzzeek]

the user is running 11g. So they should not be running instantclient 12.x then ?

[zzzeek]

also are there other problems that this mismatch might cause , so that I can sell this user on resolving it this way?

[zzzeek]

I'm pretty sure nobody would ever expect True/False to be automatically converted to Y/N. If you look at other DBAPIs, coercing True/False to 1/0 is very standard.

[zzzeek]

keep in mind, SQLAlchemy does do this conversion as well when you use the Boolean datatype, that's not the problem, the problem is this user is reading the table definition from the database and using it straight, where the datatype is SMALLINT and SQLAlchemy never has to coerce True/False in that case, since ...they're integers! hence I'm confused how this is even happening.

[anthony-tuininga]

True/False in Python are integers. What is cx_Oracle telling the oracle client library in the case of True/False ? can't it just send a 1/0 like most other DBAPIs ?

Internally, the value used is indeed 0 and 1 but Oracle has a boolean type which is only supported in PL/SQL and that is the value that is being sent, not a plain integer. This was introduced in 12c so in 11g the only option is a plain integer, which is what happens. It might be possible to adjust the type to plain integer when a plain SQL statement is being executed. I'll look into that.

the user is running 11g. So they should not be running instantclient 12.x then ?

The problem only occurs when you have 12.2 client and 12.1 database. Using 11g resolves that issue, but so does using the same level (12.2 for both or 12.1 for both) of client and database. If the database is 12.1 I'd suggest using the same client in this case -- unless I can come up with a solution that works better than the current one! And obviously using cursor.setinputsizes() will also work in this situation.

also are there other problems that this mismatch might cause , so that I can sell this user on resolving it this way?

Which mismatch? The one between client and database versions? Or between boolean and integer?

I'm pretty sure nobody would ever expect True/False to be automatically converted to Y/N. If you look at other DBAPIs, coercing True/False to 1/0 is very standard.

True. It isn't common.

keep in mind, SQLAlchemy does do this conversion as well when you use the Boolean datatype, that's not the problem, the problem is this user is reading the table definition from the database and using it straight, where the datatype is SMALLINT and SQLAlchemy never has to coerce True/False in that case, since ...they're integers! hence I'm confused how this is even happening.

It is confusing how the input is boolean values. Is the user providing these directly from their application? And if so, why? Why not use integers?

[zzzeek]

thanks for going through all my rapid fire questions!

user says they are on 11g database but having the issue with instantclient 12.x. is that expected? i get that PL/SQL has a boolean type, that makes total sense.

for cursor.inputsizes, it's again where from my perspective, I only know the datatype is "SMALLINT" and I can just setinputsizes all the "integer" types to NUMERIC across the board. The other way is that the user has to explicitly specify the datatype of Boolean for this column on the Python side. but this wasn't necessary before, because I used to setinputsizes() everything, and in #68 (comment) you sort of discouraged that, but since then we've already had a few of these cases come up where it is more helpful than not.

the user sends True/False because this column was originally declared as a Boolean, but they are losing that information (without explicitly setting it) because they are reading the table definition from the system catalogs.

[zzzeek]

I could also use an inputtypehandler, right? my concern there is that it hampers performance to run a function for all values (or would that limit itself to this new pl/sql boolean datatype? that could work).

[anthony-tuininga]

Looks like this is completely unsupported -- the use of "boolean" types for plain SQL statements and error "ORA-00932: inconsistent data types: expected NUMBER got BOOLEAN" is what you will get in this case if you use any 12c client, regardless of database. The discrepancy noted above was for use in PL/SQL. I'll look into what I can do to alleviate that issue and get back to you.

I could also use an inputtypehandler, right? my concern there is that it hampers performance to run a function for all values (or would that limit itself to this new pl/sql boolean datatype? that could work).

Yes, an input type handler can be used as well -- and you can set that at a high level (on the connection) so that all statements are affected. This only affects the bind operation so isn't a significant performance concern...but if you have a better way to determine when this situation is happening and can avoid calling the function for each bind operation, so much the better! And cursor.setinputizes() is perfectly acceptable if you know that you can determine the correct type better than the driver can, or if you want to use a type system that is different from what cx_Oracle uses. My comment was intended to discourage casual use -- but even in that comment I suggested that use by SQLAlchemy might make sense, especially if you "know better" -- and I believe the user is specifically identifying the types in question, right?

the user sends True/False because this column was originally declared as a Boolean, but they are losing that information (without explicitly setting it) because they are reading the table definition from the system catalogs.

I'm not sure exactly what this means, but are you trying to say this is an unusual situation and there is a perfectly good way to avoid this situation?

[zzzeek]

oh, I just realized inputtypehandler is going to be passed every value in all cases, that definitely is something folks complain about (e.g. when they are insert w/ executemany and 10000 rows).

So yes this is an issue that is avoidable by using a SQLAlchemy Boolean type in the correct way, however the regression on our end is that because we dropped setinputsizes() for integers, it no longer works without it. I likely should just turn on setinputsizes for ints.

[anthony-tuininga]

I've just pushed a commit that resolves this issue, I believe. If the variable is not being used inside PL/SQL it simply coerces to integer (0 or 1). Let me know if that resolves the issue for you and whether you believe cx_Oracle 6.3.2 is warranted. Thanks!

[zzzeek]

oh and I resolved it on my end w/ a more specific setinputsizes call() :) but this is still good thanks!

[anthony-tuininga]

Ok. I'm going to assume this is good to close, then. :-)