CMD prompt can't display Unicode characters that are stored in oracle database correctly

Question

I've been meddling with charset/encoding problems of Oracle database, PL/SQL developer and JDBC and these are what I want to achieve:

1. Input Unicode characters from a browser into the database
2. Display Unicode characters that are retrieved from the database correctly in the browser, CMD prompt, and PL/SQL developer

And these are what I've done:

1. Change the server charset to AL32UTF8 by following the steps:

   SQL>SHUTDOWN IMMEDIATE
   SQL>STARTUP MOUNT;
   SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
   SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
   SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
   SQL>ALTER DATABASE OPEN;
   SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;
   SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL32UTF8;
   SQL>update props$ set VALUE$='UTF8' where NAME='NLS_NCHAR_CHARACTERSET';
   SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION;
   SQL>SHUTDOWN IMMEDIATE;
   SQL>STARTUP
   

2. Change the client charset to AL32UTF8 by adding system environment variable NLS_LANG with value AMERICAN_AMERICA.AL32UTF8and changing the value of NLS_LANG to AMERICAN_AMERICA.AL32UTF8 under HKEY_LOCAL_MACHINE>SOFTWARE>ORACLE>KEY_OraDB12Home1 in regedit

3. Check "Always UTF8" under Tool>Preferences>Files>Format>Encoding in P/SD

Eventually if I added the Unicode characters to the database from either the browser or P/SD they can be displayed properly in the browser and P/SD but not in the CMD prompt. If they are added to the database from the CMD prompt they can be displayed correctly everywhere. For now I'm sure that the characters input from the browser and P/SD are Unicode characters, but not for the CMD input, which is also supposed to be Unicode since both the server and client charsets have been set to AL32UTF8. Here's an example of a correct display and an incorrect one:

In P/SD: (Correct)

SQL> select title from posts where id=134;
TITLE
--------------------------------------------------------------------------------
测试

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;
DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

In command prompt: (Incorrect)

SQL> select title from posts where id=134;

TITLE
--------------------------------------------------------------------------------
娴嬭瘯

SQL> SELECT DUMP(title, 1016) FROM posts where id=134;

DUMP(TITLE,1016)
--------------------------------------------------------------------------------
Typ=1 Len=6 CharacterSet=AL32UTF8: e6,b5,8b,e8,af,95

SQL>

Answer 1

Most likely you corrupted your database and you should consider to make a full restore from backup.

ALTER DATABASE CHARACTER SET ... was supported till Oracle version 9 and worked only at certain conditions.

Never use clause INTERNAL_USE or make any updates on Oracle system tables unless you are advised by Oracle Support. If you like to change the character set on an existing database, follow this instruction: Character Set Migration

Perhaps you had good luck, because you changed only WHERE NAME='NLS_NCHAR_CHARACTERSET'. National character set are hardly used nowadays, perhaps you did not destroy everything.

Anyway, items 2.) and 3.) are correct. In case of 32 bit check also registry key HKEY_LOCAL_MACHINE>SOFTWARE>Wow6432Node>ORACLE>KEY_OraDB12Home1

In order to verify if your data is really stored in UTF-8 run

SELECT DUMP(YOUR_COLUMN, 1016) FROM YOUR_TABLE;

And check the binary values. For comparison you can use page Unicode code converter to get UTF-8 binary values (field "UTF-8 code units") of arbitrary strings.

Answer 2

Your code page is 936 (Chinese). When you start SQL"Plus then this code page is inherited from CMD.

Usually you have two possibilities:

1. Set NLS_LANG according to your code page (either as Environment Variable or in Registry). Example set NLS_LANG=.WE8MSWIN1252 for a PC in Central Europe, the language and territory information do not matter for character sets.

   In your case this solution seems to be not possible, according this list of Character Sets there is no Oracle character set for code page 936. But have a look at this Wikipedia article Code page 1386. Perhaps you can use "GB 18030", which would be set NLS_LANG=.ZHS32GB18030 or "GB 2312", which would be set NLS_LANG=.ZHS16CGB231280

2. Second solution is to switch th code page at CMD before you launch SQL*Plus. Use chcp 65001 in order to set the code page to UTF-8. However, you may face some problems when you use UTF-8 on CMD, see this discussion.

For more information have also a look at this answer.