[ACCEPTED]-Inserting national characters into an oracle NCHAR or NVARCHAR column does not work-nvarchar

Accepted answer

Score: 26

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

Anyway: If you can't convert 25 the database:

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

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

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

Unfortunately, that 22 is not enough.

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

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

SQL Plus on Unix

These 14 environemnet variables sets up the unix 13 environment and sqlplus to use UTF-8 files, and 12 also configure sqlplus to send string literals 11 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 10 - Linux or other systems may need different 9 strings, use locale -a to list supported locales.)

JDBC Driver

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

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

SQL Developer

Locate 5 sqldeveloper.conf, and add the following 4 lines:

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

SQL Plus on Microsoft Windows

I haven't tried if SQLplus on Microsoft 3 Windows or Toad handles utf-8 at all. Sqlplusw.exe 2 may do that, and the following registry 1 settings may do the trick.

NLS_LANG=AMERICAN_AMERICA.AL32UTF8
ORA_NCHAR_LITERAL_REPLACE=true

Score: 1

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

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

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

eg:

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

The 'N' prepending the 2 string is important. Also, must set the 1 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

Source: stackoverflow.com

[ACCEPTED]-Inserting national characters into an oracle NCHAR or NVARCHAR column does not work-nvarchar

What is the point of COLLATIONS for nvarchar (Unicode) columns?

Should I be using JDBC getNString() instead of getString()?

What's the SQL national character (NCHAR) datatype really for?

Columns displayed in incorrect order when select * from table in sql developer

Is there a good alternative to SQL*PLUS for Oracle?

What's the difference between "commit" and "commit work"?

Convert epoch to date in sqlplus / Oracle

SET SCAN OFF purpose

Referencing a two column primary key with multiple foreign keys

How can I check the last time stats was run on Oracle without using OEM