Inserting national characters in an NCHAR or NVARCHAR oracle column does not work

Question

When inserting rows into the oracle database, some national characters are replaced by question marks, even if they are inserted in the NCHAR or NVARCHAR column, which should be able to process all Unicode characters.

This happens using Oracle SQL Developer, sqlplus, or using the JDBC driver.

WE8ISO8859P1 (West European iso-8859-1) is installed in the NLS_CHARACTERSET database. NLS_NCHAR_CHARACTERSET used for NCHAR columns is set to AL16UTF16. (UTF-16)

Any character not in NLS_CHARACTERSET seems to be replaced by an inverted question mark.

Answer 1

Edit: note that the best way to handle UTF in Oracle is to create a database using the AL32UTF8 character set of the database and using regular varchar2 columns. One of the problems with using nchar columns is that the oracle cannot use indexes for regular char / varchar2 columns when arguments are sent as nchar by default.

In any case: if you cannot convert the database:

---

Firstly, unicode literals must have the prefix "n", for example:

select n'Language - Språk - Język' from dual; 

*) 8-bit encodings cannot process this text

Unfortunately, this is not enough.

For some reason, the default behavior for database clients is to translate all string literals into the database character set, so that the values ​​will be changed before the database sees the string.

Clients need some configuration in order to be able to insert a Unicode character in an NCHAR or NVARCHAR column:

SQL Plus on Unix

These environmentemnet variables set the unix and sqlplus environment to use UTF-8 files, as well as configure sqlplus to send string literals to unicode.

 NLS_LANG=AMERICAN_AMERICA.AL32UTF8 LC_CTYPE="en_US.UTF-8" ORA_NCHAR_LITERAL_REPLACE=true 

(en_US.UTF-8 for Solaris - Linux or other systems may need different lines, use locale -a to display supported locales.)

Jdbc driver

Applications that use the Orales JDBC driver must have the following system property defined for sending Unicode string literals.

 -Doracle.jdbc.defaultNChar=true -Doracle.jdbc.convertNcharLiterals=true 

SQL developer

Locate sqldeveloper.conf and add the following lines:

 AddVMOption -Doracle.jdbc.defaultNChar=true AddVMOption -Doracle.jdbc.convertNcharLiterals=true 

SQL Plus on Microsoft Windows

I have not tried if SQLplus on Microsoft Windows or Toad handles utf-8 at all. Sqlplusw.exe can do this, and the following registry settings can do the trick.

 NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ORA_NCHAR_LITERAL_REPLACE=true 

Answer 2

Thanks KarlP - it made me go. Repeating what worked for me.

Insert Chinese (any utf8) text into a nvarchar column of a non-unicode database (ex: ISO8859, etc.) using sqlplus for linux.

These are db options on my system, pay attention to single-byte encoding for char, but multi-byte for nchare. NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

eg:

 INSERT INTO tt values ( N'气前照灯' ); 

The "N" appending line is important. In addition, before running sqlplus, you must install env,

 # Important to tell sqldeveloper what encoding is needed. export NLS_LANG=AMERICAN_AMERICA.UTF8 # Others might find AMERICAN_AMERICA.AL32UTF8 or whatever better suits. # ** THIS MATTERS - DOES NOT WORK WITHOUT !! export ORA_NCHAR_LITERAL_REPLACE=true