Need to know if binding to integers NATIVE_INT causes performance issue or other problem

[zzzeek]

It seems that in cx_Oracle 6.x we now lose numeric precision for large integers if we bind to cx_Oracle.NUMBER, we now have to bind to cx_Oracle.NATIVE_INT, example below.

If I change SQLAlchemy to bind all integers to NATIVE_INT instead of NUMBER, what surprises are there? does this type perform very poorly or not function in all cases?

import cx_Oracle

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

cursor = conn.cursor()

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

cursor.execute("""
CREATE TABLE t (
    x INTEGER
)
""")


# this works.
# cursor.setinputsizes(x=cx_Oracle.NATIVE_INT)

# this no longer does.
cursor.setinputsizes(x=cx_Oracle.NUMBER)

cursor.execute("insert into t(x) values (:x)", {"x": 1376537018368127})

cursor.execute("select x from t")
value = cursor.fetchone()[0]

# on cx_oracle 6.2 returns 1376537018368130 unless you used
# NATIVE_INT or did nothing
assert value == 1376537018368127, value

[anthony-tuininga]

I see that there is indeed a difference between cx_Oracle 5.3 and cx_Oracle 6.4 regarding the cursor.setinputsizes(x=cx_Oracle.NUMBER) behaviour. I'll look into why that would be and report back. In the meantime, just not calling setinputsizes() works as expected -- which I think you already know. You can also use cursor.setinputsizes(x=int) which is a better solution than using cursor.setinputsizes(x=cx_Oracle.NATIVE_INT). The reason is that Oracle has optimised use of the NUMBER type but the NATIVE_INT type has a slight performance drop -- and the use of "int" just tells cx_Oracle that only integers will be accepted, but the Oracle NUMBER type is still used. I hope that's clear!

[zzzeek]

OK ill put "int" in there for now

[anthony-tuininga]

The reason for the difference between the two versions (5 and 6) is due to the adoption of ODPI-C and the simplification of the code in the cx_Oracle code base. In version 5, using cx_Oracle.NUMBER for a variable effectively used Oracle NUMBER and the cx_Oracle code would make OCI calls to transform integer, float and decimal values to Oracle NUMBER values. In version 6, using cx_Oracle.NUMBER for a variable effectively means double floating point and you can no longer mix and match floating point, integer and decimal values in a single variable. ODPI-C requires that this be specified when the variable is created and helps to eliminate uncertainty about how the data is represented (with the loss of a bit of flexibility). So the use of "int" in cursor.setinputsizes() for this case is exactly what you want. It should work just as effectively in cx_Oracle 5, too.

[zzzeek]

yep have already released the workaround thanks!