[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.


(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.


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.

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


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.
# Others might find AMERICAN_AMERICA.AL32UTF8 or whatever better suits.


More Related questions