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!