DB_TYPE_NVARCHAR recipe from cx_Oracle does not seem to work with oracledb #12
Description
hey Anthony -
we here are still working with the changes you suggested in oracle/python-cx_Oracle#596. These are working for cx_Oracle but failing for oracledb.
Test script:
#import cx_Oracle
import oracledb as cx_Oracle
import random
conn = cx_Oracle.connect(
user="scott",
password="tiger",
dsn=cx_Oracle.makedsn("oracle18c", 1521, service_name="xe"),
)
cursor = conn.cursor()
try:
cursor.execute("drop table long_text")
except:
pass
cursor.execute(
"""
CREATE TABLE long_text (
x INTEGER,
y NCLOB,
z INTEGER
)
"""
)
# the third character is the failure character
word_seed = "ab🐍’«cdefg"
data = " ".join(
"".join(random.choice(word_seed) for j in range(150))
for i in range(100)
)
# succeeds
# cursor.setinputsizes(**{"y": cx_Oracle.NCLOB})
# fails with oracledb only:
# ORA-01461: can bind a LONG value only for insert into a LONG column
cursor.setinputsizes(**{"y": cx_Oracle.DB_TYPE_NVARCHAR})
# no setinputsizes: fails on oracledb only with:
# ORA-01483: invalid length for DATE or NUMBER bind variable
cursor.execute(
"INSERT INTO long_text (x, y, z) VALUES (:x, :y, :z)",
{"x": 5, "y": data, "z": 10},
)
With cx_Oracle. all three setinputsizes patterns: using NCLOB, using DB_TYPE_NVARCHAR, not calling setinputsizes, all succeed.
using oracledb: NCLOB succeeds, DB_TYPE_NVARCHAR produces "ORA-01461: can bind a LONG value only for insert into a LONG column", and not using setinputsizes produces "ORA-01483: invalid length for DATE or NUMBER bind variable".
I am trying to integrate every improvement suggested in the above mentioned issue as can be seen in this patch: https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/3903 tests all succeed for cx_Oracle but we have those failures for oracledb.
FWIW, before I made those changes, we do have oracledb passing all tests that cx_Oracle passes, very good!
