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

Question

When inserting strings in an oracle database, some national characters are replaced with question marks, even though they are inserted in an NCHAR or NVARCHAR column - that should be able to handle all Unicode characters.

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

The database NLS_CHARACTERSET is set to WE8ISO8859P1 (western european iso-8859-1) The NLS_NCHAR_CHARACTERSET used for NCHAR columns is set to AL16UTF16. (UTF-16)

Any character not in the NLS_CHARACTERSET seems to be replaced with a inverted question mark.

Solution 1

Edit: Note that the best way to handle UTF on Oracle is to create the database using the database character set AL32UTF8, and use ordinary varchar2 columns. One of the problems with using nchar columns is that oracle can't use indexes for ordinary char/varchar2 columns when arguments are sent as nchar by default.

Anyway: If you can't convert the database:

---

First, unicode literals needs to be prefixed with an 'n', like this:

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

*) 8-bit encodings can't handle this text

Unfortunately, that is not enough.

For some reason, the default behaviour for database clients is to translate all string literals to the database character set, meaning that values will be changed even before the database gets to see the string.

The clients need some configuration in order to be able to insert a unicode character into an NCHAR or NVARCHAR column:

SQL Plus on Unix

These environemnet variables sets up the unix environment and sqlplus to use UTF-8 files, and also configure sqlplus to send string literals in unicode.

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

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

JDBC Driver

Applications using Oracles JDBC driver needs to have the following system property defined to send strings literals in unicode.

-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 haven't tried if SQLplus on Microsoft Windows or Toad handles utf-8 at all. Sqlplusw.exe may do that, and the following registry settings may do the trick.

NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=true

Solution 2

Thanks KarlP - that got me going. Recapping what worked for me.

Inserting chinese ( any utf8 ) text into an nvarchar column of a non-unicode database ( eg: ISO8859 etc ), using sqlplus on linux.

These db params on my system, note a single byte encoding for char, but multibyte for nchare. NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16

eg:

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

The 'N' prepending the string is important. Also, must set the env before starting sqlplus,

# 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

Comments

[CyprUS]

Can you explain how to do it for Microsoft SQL Express 2005 ?

[KarlP]

Not generally. Regarding Java, the jdbc drivers and visual studio is working out of the box, if the column type is nchar and literals are prefixed with n.

[Fedor Alexander Steeman]

Do you know how to set this for .NET System.Data.OracleClient drivers?

[KarlP]

Not really. If possible, use NLS_CHARACTERSET AL32UTF8 when creating the database and avoid nchar columns. The document "OCI Programming with Unicode" may have some information.

[L. Guthardt]

This is not how an answer is supposed to be. An answer is supposed to show the OP a solution just for his issue, but it already exists a well suiting solution. Your answer is redundant and doesn belong here. I know you are new to Stack Overflow, but that's not how an answer is commonly accepted here. Sorry, but I hope you do understand me, please consider removing your answer. :-)