Can't insert into NVARCHAR2 datatype

[rbrodewicz]

Can't insert into nvarchar2 data. Was trying to play with NLS_LANG, and python encoding/nencoding for connect object. Nothing works.
Querying data works.
Test case below (fetch value from DB, insert save value into DB).

DB

create table rbx (n nvarchar2(100));
insert into rbx (n) values(utl_raw.cast_to_nvarchar2('0105017C015B017A01070144014200F30119'));
commit;

select n, utl_raw.cast_to_raw(n) r from rbx;

N          R
---------- ----------------------------------------
ążśźćńłóę  0105017C015B017A01070144014200F30119

select * from nls_database_parameters
where parameter in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               WE8ISO8859P1
NLS_NCHAR_CHARACTERSET         AL16UTF16

Python

import cx_Oracle

f8  = open('out8', 'w', encoding="utf-8")
f16 = open('out16','w', encoding="utf-16")

con = cx_Oracle.connect("user", "pass", "db", encoding='UTF-8', nencoding='UTF-16')
cur = con.cursor()

n = cur.execute("select n from rbx").fetchone()[0]

f8.write(n)
f16.write(n)

cur.prepare('insert into rbx (n) values (:n)')
cur.execute(None, {'n':n})

con.commit()

cur.close()
con.close()

DB

select n, utl_raw.cast_to_raw(n) r from rbx;

N          R
---------- ----------------------------------------
ążśźćńłóę  0105017C015B017A01070144014200F30119
¿¿¿¿¿¿¿ó¿  00BF00BF00BF00BF00BF00BF00BF00F300BF

1. What is your version of Python? Is it 32-bit or 64-bit?
   64

2. What is your version of cx_Oracle?
   6.0.2

3. What is your version of the Oracle client (e.g. Instant Client)? How was it
   installed? Where is it installed?
   11.2.0.4 Oracle client installation

4. What is your version of the Oracle Database?
   11.2.0.4

5. What is your OS and version?
   Windows 7 x64 / Red Hat 6.8

6. What compiler version did you use? For example, with GCC, run
   gcc --version.
   4.4.7 (for Linux)

7. What environment variables did you set? How exactly did you set them?
   None, NLS_LANG

8. What error(s) you are seeing?
   No error

[anthony-tuininga]

This is expected behaviour. Your CHAR encoding (WE8ISO8859P1) is incapable of handling the data you are trying to insert. Unless you state otherwise, the CHAR encoding is what is used for all strings in Python. You can overcome this by using cursor.setinputsizes() as in the following code snippet (add just before the call to cursor.execute() is made):

cursor.setinputsizes(n = cx_Oracle.NCHAR)

This will tell cx_Oracle that you wish to use the NCHAR encoding for your data and then it will not be converted first to the CHAR encoding for transport to the database.

[zzzeek]

Hi Anthony -

Having this issue w a user in SQLAlchemy. Is there any condition under which one would not want to setinputsizes w NCHAR when referring to an NVARCHAR2 column ? I can make this setting unconditional if not.

[anthony-tuininga]

I would think that the use of setinputsizes() for NCHAR/NVARCHAR2 columns would be useful at all times since the default is always CHAR/VARCHAR2 and, depending on the character sets in use, that could be a problem. If the CHAR/VARCHAR2 character set is a universal character set (UTF-8 for example) then it wouldn't actually cause problems -- but even then in order to avoid unnecessary conversions it would be helpful. When you bind CHAR/VARCHAR2 the Oracle client will convert the input character set to the CHAR/VARCHAR2 character set, and then will convert the CHAR/VARCHAR2 character set into the NCHAR/NVARCHAR2 character set. So telling the Oracle client that you already are using the NCHAR/NVARCHAR2 character set saves a conversion.

I think that more than answers the question! :-)

[zzzeek]

hey anthony -

user is now reporting cx_Oracle.NCHAR is not present for their cx_Oracle (waiting on version info). What version was this token added?

[anthony-tuininga]

Those were first added in 5.2 (June 2015).

[zzzeek]

Hi Anthony -

setting NCHAR unconditionally fails here:

conn = cx_Oracle.connect(
    dsn=cx_Oracle.makedsn("oracle1120", 1521), user="scott", password="tiger")
cursor = conn.cursor()
cursor.setinputsizes(day=cx_Oracle.NCHAR)

result = cursor.execute("SELECT trunc(sysdate, :day) - :back FROM DUAL",
                        {'day': 'dd', 'back': 1}).fetchall()
cursor.close()
conn.close()

output:

cx_Oracle.DatabaseError: ORA-01899: bad precision specifier

this would mean the entire approach I took based on #119 (comment) needs to be revised once again can you please advise ?

[anthony-tuininga]

Why are you using NCHAR for a format specifier?

[zzzeek]

because "dd" is a Python unicode object

[zzzeek]

it's looking like the user will have to cast 'dd' to String, SQLAlchemy can't make this decision

[anthony-tuininga]

With Python 3, every string is a unicode object....but I certainly wouldn't recommend using NCHAR across the board in Python 3! NCHAR should only be used when the Oracle database object requires it. I find it interesting that it doesn't work in your case...but its not good practice anyway. You should use CHAR/VARCHAR2 where the Oracle database object is CHAR/VARCHAR2 and NCHAR/NVARCHAR2 only when the Oracle database object is NCHAR/NVARCHAR2. This certainly isn't one of those cases!

[anthony-tuininga]

Frankly, if you can get away with it, the best thing to do is use a universal database character set (AL32UTF8) and just avoid NCHAR/NVARCHAR2 completely!

[zzzeek]

So here is the thing to understand about SQLAlchemy, is that in many cases, the user is passing string SQL and string parameters. I have no idea if these unicode objects have non-ascii characters in them and we are trying to sheild the user from making driver-level decisions.

in this case it looks like a driver-level decision based on the content of a bound parameter and how it is being used is unavoidable.

originally, in my upstream issue i was considering that the NCHAR setting could be based on some user-defined "yes-please-set-this-aggressively" kind of flag but apparently I misunderstood your answer above that NCHAR for all unicode values is universally safe.

[anthony-tuininga]

It took me a while to get my head wrapped around the issue myself so I'm not surprised that its confusing you, too! I understand that SQLAlchemy cannot know whether the database object which is being inserted or the procedure being called expects NCHAR data. The user does have to tell you, unfortunately. And you shouldn't use NCHAR exclusively as if the database object is CHAR and you bind as NCHAR then you have conversion going on and unnecessary overhead, just like if the database object is NCHAR and you bind as CHAR you have conversion and unnecessary overhead.

NCHAR was developed many years ago to handle Unicode data back when the database character sets were single byte character sets. Since then multibyte character sets like AL32UTF8 can be used as the primary database character set, and in fact this is now the default! In that case, CHAR is perfectly capable of handling non-ASCII characters! And in fact this is what I use exclusively now. I avoid NCHAR like the plague that it is. :-) If your database primary character set is a single-byte character set, though, and you want to store strings that don't fit in that character set you have no choice but to use NCHAR, but this should be occurring less and less frequently as time progresses.

It might make sense for you to check to see if the database character set is AL32UTF8 (the current default) and if so, ignore NCHAR completely by default. At that point, even if the user has set up NCHAR columns in tables anyway it won't fail. It will just be unnecessary conversion. And the user can solve that by simply migrating away from NCHAR columns completely.

I hope that make sense now and I'm sorry if my earlier comments were confusing!