Paste chinese symbols to oracle DB

Question

I am trying to paste chinese symbols to oracle DB:

NLS_NCHAR_CHARACTERSET = 'UTF8'
NLS_LANGUAGE = 'RUSSIAN'
NLS_TERRITORY = 'RUSSIA'
NLS_CURRENCY = '?.'
NLS_ISO_CURRENCY = 'RUSSIA'
NLS_NUMERIC_CHARACTERS = '',''
NLS_CHARACTERSET = 'CL8MSWIN1251'
NLS_CALENDAR = 'GREGORIAN'
NLS_DATE_FORMAT = 'DD.MM.RR'
NLS_DATE_LANGUAGE = 'RUSSIAN'
NLS_SORT = 'RUSSIAN'
NLS_TIME_FORMAT = 'HH24:MI:SSXFF'
NLS_TIMESTAMP_FORMAT = 'DD.MM.RR HH24:MI:SSXFF'
NLS_TIME_TZ_FORMAT = 'HH24:MI:SSXFF TZR'
NLS_TIMESTAMP_TZ_FORMAT = 'DD.MM.RR HH24:MI:SSXFF TZR'
NLS_DUAL_CURRENCY = '?.'
NLS_COMP = 'BINARY'
NLS_LENGTH_SEMANTICS = 'BYTE'
NLS_NCHAR_CONV_EXCP = 'FALSE'
NLS_RDBMS_VERSION = '11.2.0.4.0'

Query:

update my_table set message = '© § english пепелац 百' where id = 252615

Message field type is NVARCHAR2

In result field contains: '© § english пепелац ?'. No chinese symbol.

But if I paste '百' symbol by hand (Open my_table for edit in PL/SQL Developer, paste value to cell and commit), it works, db returns: '© § english пепелац 百'

I tried to set NLS_LANG as ".zhs16gbk", ".AL32UTF8", "SIMPLIFIED CHINESE_CHINA.ZHS16GBK" and other ..., but it not helps.

PL/SQL Developer 10.0.5.1710; Windows 7 (64-bit) 6.1 Build 7601 (Service Pack 1); oracle client 11.2.0;

Help ヽ(°□° )ノ

Answer 1

Set your NLS_LANG value to RUSSIAN_RUSSIA.AL32UTF8 (language and territory are up to you, just ensure .AL32UTF8)

Then in PL/SQL Developer go to Configure -> Preferences -> Files -> Format -> Encoding

and set "Always UTF8"

When you use "Always ANSI" or "Autodetect" then PL/SQL Developer will save the SQL script in ANSI codepage, most likely CP1252 (see https://www.microsoft.com/resources/msdn/goglobal/default.mspx), which does not support Chinese characters.

Note, UTF-8 is Oracle character set AL32UTF8. Oracle character set UTF8 means CESU-8 which is slightly different.

Don't care that Database (national) character set is CESU-8 whereas your client character set is UTF-8, the Oracle driver makes automatic conversion - that's the main purpose of NLS_LANG setting.