Stefan Schwarzer (@sschwarzer) wrote:

About the second API:

If I understand the documentation at

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/nlspg/supporting-multilingual-databases-with-unicode.html#GUID-95787DA3-08D4-41A7-9418-2A36C602014E

correctly, an Oracle NVARCHAR2 implies a cx_Oracle.NCHAR type. If you do an SQL SELECT on an NVARCHAR2 column in cx_Oracle, the cursor description contains the cx_Oracle.NCHAR datatype for the NVARCHAR2 column.

So a use_nchar parameter for sqlalchemy.dialects.oracle.NVARCHAR2 would be redundant, wouldn't it be?

Generally, as I understand the matter so far, Unicode in SQLAlchemy and N... string data types are orthogonal. For example, you can have a VARCHAR2 (not NVARCHAR2) column in the database which probably should be a unicode string if the database encoding NLS_CHARACTERSET is the recommended AL32UTF8.

So according to my current understanding, there are three different concepts (where the last two are always correlated for cx_Oracle):

• The user wants the data, as it's visible on the Python side, to be unicode strings (only relevant for Python 2). I think this is what the use of SQLAlchemy's Unicode type means. Also, for a Unicode column, SQLAlchemy will use NVARCHAR2, not VARCHAR2, when creating a table with this column.

• The datatype of the column is one of the N... character types, so it uses Oracle's NLS_NCHAR_CHARACTERSET, not the database character set NLS_CHARACTERSET.

• For an N... character type, cx_Oracle uses its NCHAR type, see https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.NCHAR , which mentions only NVARCHAR2. I haven't verified if NCHAR is also used for NCLOB.

Michael Bayer (@zzzeek) wrote:

Setting NCHAR across the board for all NVARCHAR2 would certainly be easiest. I've asked Anthony on the github issue.

Michael Bayer (@zzzeek) wrote:

here's a review at https://gerrit.sqlalchemy.org/#/c/zzzeek/sqlalchemy/+/633 that will test this new setting.

Michael Bayer (@zzzeek) wrote:

also if you could try that patch locally and see if it fixes your issue.

Stefan Schwarzer (@sschwarzer) wrote:

Apart from NVARCHAR2, there are two other national character datatypes for Oracle databases, NCHAR (fixed length) and NCLOB (several GB). See https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm .

For testing the cx_Oracle types for these columns, I created a table

CREATE TABLE NCHAR_TEST (
  NVARCHAR2_COL NVARCHAR2(10),
  NCHAR_COL NCHAR(10),
  NCLOB_COL NCLOB
)

and ran SELECT * FROM NCHAR_TEST directly from cx_Oracle. The cx_Oracle datatypes for the columns, taken from Cursor.description, are:

• NVARCHAR2 -> cx_Oracle.NCHAR (already known)
• NCHAR -> cx_Oracle.FIXED_NCHAR
• NCLOB -> cx_Oracle.NCLOB

Do any of these need to be considered (in addition to NVARCHAR2)? Please let me know if I should test the current patch or a later one that takes more datatypes into account.

Michael Bayer (@zzzeek) wrote:

these have already been included in setinputsizes, I only added NCHAR:

self._include_setinputsizes = {
cx_Oracle.NCLOB, cx_Oracle.CLOB, cx_Oracle.LOB,
cx_Oracle.BLOB, cx_Oracle.FIXED_CHAR, cx_Oracle.TIMESTAMP
}

important thing is patch solves your problem, right? it passes CI so is good on my end.

Michael Bayer (@zzzeek) wrote:

oh FIXED_NCHAR vs. FIXED_CHAR, can add that sure

Michael Bayer (@zzzeek) wrote:

this is added to the review

Michael Bayer (@zzzeek) wrote:

Use NCHAR + setinputsizes() for all NVARCHAR2

The cx_Oracle dialect now calls setinputsizes() with cx_Oracle.NCHAR
unconditionally when the NVARCHAR2 datatype, in SQLAlchemy corresponding
to sqltypes.Unicode(), is in use. Per cx_Oracle's author this allows
the correct conversions to occur within the Oracle client regardless
of the setting for NLS_NCHAR_CHARACTERSET.

Change-Id: I3989b7aaf2178c263015a7433939196b76baf1e4
Fixes: #4163

→ ff4898f

Changes by Michael Bayer (@zzzeek):

• changed status to closed

Stefan Schwarzer (@sschwarzer) wrote:

Your fix works for me.

(Sorry, I wasn't able to try it earlier.)

Thank you! :-)

Michael Bayer (@zzzeek) wrote:

OK you have a workaroudn for now until I release 1.2.2 ?

Stefan Schwarzer (@sschwarzer) wrote:

Yes, no problem. :)

Michael Bayer (@zzzeek) wrote:

this issue is going to have to be re-done entirely, see the discussion at https://bitbucket.org/zzzeek/sqlalchemy/issues/4242/nchar-cant-be-bound-for-every-string-value#comment-44855224

Michael Bayer (@zzzeek) wrote:

we will bind NCHAR when the NVARCHAR2 etc. datatypes are used, but if generic Unicode is passed, it will only bind to NCHAR if use_nchar_for_unicode=True is passed (e.g. my first idea above).

The change however de-emphasizes NCHAR at the DDL level as well so I'm going to keep it in 1.3.