I've been meddling with charset/encoding problems of Oracle database, PL/SQL developer and JDBC and these are what I want to achieve:
- Input Unicode characters from a browser into the database
- 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:
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
Change the client charset to
AL32UTF8
by adding system environment variableNLS_LANG
with valueAMERICAN_AMERICA.AL32UTF8
and changing the value ofNLS_LANG
toAMERICAN_AMERICA.AL32UTF8
underHKEY_LOCAL_MACHINE>SOFTWARE>ORACLE>KEY_OraDB12Home1
in regedit- 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>
CMD prompt
?cmd.exe
can display those characters? Did you switch the cmd codepage to Unicode?chcp
in your CMD terminal?