Analysis of Oracle Character Set garbled characters and Solutions

Oracle Character Set garbled problem analysis and solution 1. Problem description SQLPlusWorkSheet is an SQL statement editor for the window graphic interface. for users who like the window interface but do not like the character interface, this tool is quite popular with SQLPLUS. However, after Oracle8i, if Oracle8i is installed

Oracle Character Set garbled problem analysis and solution 1. Problem description SQL Plus WorkSheet is an SQL statement editor for the window graphic interface. for users who like the window interface but do not like the character interface, this tool is quite popular with SQL/PLUS. However, after Oracle 8i, if Oracle 8i is installed

Analysis of Oracle Character Set garbled characters and Solutions

I. Problem Description

　　SQL Plus WorkSheetIt is an SQL statement editor for window graphic interfaces. For those users who like window interfaces but do not like character interfaces, this tool is quite popular with SQL/PLUS. However, after Oracle 8i, if the English character set is selected when Oracle 8i is installed, for China, the simplified Chinese character set (ZHS16GBK) is usually selected. After the installation is successful, when you run the SQL Plus WorkSheet program, all Chinese characters are displayed and the query results are garbled.

Ii. Problem Analysis

At first, the problem occurs. First, it is suspected that the character set is incorrect during installation, that is, the simplified Chinese character set is not correctly set. First, check the database character set. In SQL/PLUS, run the following SQL statement to check the character set of the connected database:

SQL> select userenv ('language') from dual;

USERENV ('language ')

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

The query results show that the character set selected during database installation is ZHS16GBK, which indicates that the character set is correctly set during installation. Step 2: Check the client registry and open the Registry Editor (RegEdit). In HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_LANG, the Oracle client character set is AMERICAN_AMERICA.ZHS16GBK, the setting is also completely correct, which can eliminate the problem of incorrect Character Set settings on the client. At the same time, the query character set in SQL/PLUS is displayed completely normal on the same client machine. This also shows that it is not a problem in Character Set setting, but a problem in the system program SQL/Plus Worksheet.

In versions earlier than Oracle 8i, this has never been the case. This should be related to the Oracle version, we know that a significant difference between Oracle 8i and earlier versions is that most of the Oracle system programs use Java drivers. In fact, this is the root cause of Character Set garbled characters. A non-Java-driven program, such as SQL * Plus, has a system parameter NLS_LANG. this parameter is set in the environment variable in the UNIX system and in the Registry in the Windows operating system, this parameter determines the character set of the client application.For Java-based applications, such as SQL * PLus Worksheet, The NLS_LANG parameter does not have any effect on such programs.

Iii. Solutions

After finding out the cause of the problem, we will discuss how to solve it. For all tools in Oracle Enterprise Manager, the configuration file name is dbappscfg. properties. You can modify the file to solve the above problem. The file is located in the $ ORACLE_HOME \ sysman \ config directory. open the file in any text editor and find this file,

# SQLPLUS_NLS_LANG = AMERICAN_AMERICA.WE8ISO8859P1

Remove the annotator # and change it

SQLPLUS_NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

For Windows operating systems, you also need to modify one item. Find # SQLPLUS_SYSTEMROOT = c: \ WINNT40 in the file, remove the annotator, and change it to the operating system home directory of your machine. If the main directory of the operating system is in Winnt of drive D, change it

SQLPLUS_SYSTEMROOT = d: \ WINNT.

Modifications to the next item only apply to the Windows operating system, and are not required for the UNIX operating system. If this option is not modified in Windows, the following error is prompted during system connection in Oracle Enterprise Manager:

ORA-12560 TNS: protocol adapter error

Or

ORA-12545 Connect failed because target host or object does not exist

After the modification is complete, save the file and exit the editing. Reconnect SQL PLUS Worksheet to solve the character set garbled problem and display the correct simplified Chinese character set.