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

character-setencodingoraclesqlplus

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>

Best Answer

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.

Related Question