Analysis and solution of the problem of Oracle character set garbled

analysis and solution of the problem of Oracle character set garbled
I. Description of the problem

　　SQL Plus Worksheet is an SQL Statement editor for a window graphical interface, which is relatively sql/plus for users who prefer a window interface to a character interface. But after Oracle 8i, if Oracle 8i is installed with the English character set, the Simplified Chinese character set (ZHS16GBK) is usually selected for us in China, and after the installation is successful, the SQL Plus worksheet program is run, There will be all the Chinese display and the query results are garbled.

Second, the problem analysis

Initially, this problem was first suspected of having problems with the character set setting at installation time, which meant that the correct Simplified Chinese character set was not established. First check the database character set, and 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 found that the character set selected for the database installation is Simplified Chinese ZHS16GBK, which indicates that the character set setup is completely correct. The second step begins to suspect User Client character set problem, check client registry, open Registry Editor (RegEdit), in Hkey_local_machine/software/oracle/nls_lang, Oracle client character set is found to be american_america. ZHS16GBK, the settings are also completely correct, you can exclude the problem that the client character set setting is wrong. At the same time there is also a phenomenon in the same client machine Sql/plus query character set display completely normal, which also shows that it is not a character set problem, but the System program Sql/plus worksheet problem.

This has never happened in previous versions of Oracle 8i, which should be related to the Oracle version, and we know that a significant difference between Oracle 8i and its previous version is that most Oracle system programs are now powered by Java, In fact, this is the root cause of the problem of character set garbled. Non-Java-driven programs, such as Sql*plus, have a system parameter Nls_lang, which is set in the UNIX system in the environment variable and set in the registry in the Windows operating system, which determines the character set of the client application. for Java-based applications, such as the Sql*plus Worksheet,nls_lang parameters that are now encountered have no effect on such programs.

Third, the solution

After you have found the cause of the problem, let's discuss how to resolve the problem. For all the tools in Oracle Enterprise Manager, there is a configuration file named Dbappscfg.properties that modifies the file to resolve the problem. This file is located in the $oracle_home\sysman\config directory, with any text editor to open the file, in this file, find such an item,

# Sqlplus_nls_lang=american_america. We8iso8859p1

Remove the annotation character # and change it to

Sqlplus_nls_lang=american_america. Zhs16gbk

For Windows operating systems, you also need to modify an item to find # SQLPLUS_SYSTEMROOT=C:\\WINNT40 in the file, remove the annotation character, and modify it to the operating system home directory of your machine. such as the operating system's home directory in the D-Disk under the Winnt, it will be modified to

Sqlplus_systemroot=d:\\winnt.

Modifications to the subsequent entry are only made to the Windows operating system and are not required for UNIX operating systems. If you do not modify the entry in the Windows operating system, in Oracle Enterprise Manager, the following error is prompted when connecting to the system:

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 edit. Reconnect SQL PLUS worksheet, the character set garbled problem is resolved and the correct Simplified Chinese character set is displayed.