Analysis of the problem of Oracle 8i character set and its solution
- 2020-05-24 06:23:20
- OfStack
The ORACLE tutorial you are looking at is :Oracle 8i character set problem analysis and its solution. 1. Problem description
SQL Plus WorkSheet is a one-window graphical SQL statement editor. For users who prefer the window interface to the character interface, SQL/PLUS has been very popular. However, after the installation of Oracle 8i, if the Oracle 8i is installed with a character set other than the English character set, we in China usually select the simplified Chinese character set (ZHS16GBK). After the successful installation, running SQL Plus WorkSheet will result in all the Chinese language display and query results are all scrambled.
2. Problem analysis
The problem was first suspected to be a character set problem during installation, that is, the correct simplified Chinese character set was not set. First, check the database character set. In SQL/PLUS, run the following SQL statement to check the character set of the database you are connecting to:
The result of the query shows that the character set selected for the database installation is ZHS16GBK (simplified Chinese). Step 2 began to suspect that the user client character set, check the client registry, open the registry editor (RegEdit), in HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_LANG, found Oracle client character set to AMERICAN_AMERICA ZHS16GBK, set is entirely correct, can rule out is the client character set wrong question. At the same time, another phenomenon is that the query character set in SQL/PLUS on the same client machine is completely normal, which also indicates that it is not the character set setting problem, but the system program SQL/Plus Worksheet problem.
In the previous version of Oracle 8i, there has never been such a situation, which should be related to the version of Oracle. We know that the significant difference between Oracle 8i and its previous version 1 is that most Oracle system programs are now driven by Java. In fact, this is the root cause of the character set chaos problem. Programs that are not Java driven, such as SQL*Plus, have one system parameter, NLS_LANG, which is set in the environment variable on UNIX and in the registry on Windows operating system. This parameter determines the character set of the client application. For applications based on Java, such as SQL*PLus Worksheet now encountered, NLS_LANG parameter does not have any effect on such applications.
3. Solutions
Having identified the cause of the problem, let's discuss how to solve it. For all the tools in Oracle Enterprise Manager, there is a configuration file named dbappscfg.properties, which can be modified to solve the above problem. This file is located in the $ORACLE_HOME\sysman\config directory. Open the file with any text editor.
Remove the comment # and change it to SQLPLUS_NLS_LANG= AMERICAN_AMERICA.ZHS16GBK.
For the Windows operating system, one more change is needed. Find # SQLPLUS_SYSTEMROOT=c:\\WINNT40 in the file. Remove the comment and change it to the operating system home directory of your machine. If the operating system's home directory is under Winnt on D disk, change it to SQLPLUS_SYSTEMROOT=d:\ WINNT.
The modification in the last item only applies to the Windows operating system, but not to the UNIX operating system. If this item is not modified in Windows, the following error will appear when connecting to the system in Oracle Enterprise Manager:
or
When the changes are complete, save the file and exit editing. Reconnect SQL PLUS Worksheet, the character set garble problem is resolved, and the correct simplified Chinese character set is displayed.
SQL Plus WorkSheet is a one-window graphical SQL statement editor. For users who prefer the window interface to the character interface, SQL/PLUS has been very popular. However, after the installation of Oracle 8i, if the Oracle 8i is installed with a character set other than the English character set, we in China usually select the simplified Chinese character set (ZHS16GBK). After the successful installation, running SQL Plus WorkSheet will result in all the Chinese language display and query results are all scrambled.
2. Problem analysis
The problem was first suspected to be a character set problem during installation, that is, the correct simplified Chinese character set was not set. First, check the database character set. In SQL/PLUS, run the following SQL statement to check the character set of the database you are connecting to:
The result of the query shows that the character set selected for the database installation is ZHS16GBK (simplified Chinese). Step 2 began to suspect that the user client character set, check the client registry, open the registry editor (RegEdit), in HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/NLS_LANG, found Oracle client character set to AMERICAN_AMERICA ZHS16GBK, set is entirely correct, can rule out is the client character set wrong question. At the same time, another phenomenon is that the query character set in SQL/PLUS on the same client machine is completely normal, which also indicates that it is not the character set setting problem, but the system program SQL/Plus Worksheet problem.
In the previous version of Oracle 8i, there has never been such a situation, which should be related to the version of Oracle. We know that the significant difference between Oracle 8i and its previous version 1 is that most Oracle system programs are now driven by Java. In fact, this is the root cause of the character set chaos problem. Programs that are not Java driven, such as SQL*Plus, have one system parameter, NLS_LANG, which is set in the environment variable on UNIX and in the registry on Windows operating system. This parameter determines the character set of the client application. For applications based on Java, such as SQL*PLus Worksheet now encountered, NLS_LANG parameter does not have any effect on such applications.
3. Solutions
Having identified the cause of the problem, let's discuss how to solve it. For all the tools in Oracle Enterprise Manager, there is a configuration file named dbappscfg.properties, which can be modified to solve the above problem. This file is located in the $ORACLE_HOME\sysman\config directory. Open the file with any text editor.
Remove the comment # and change it to SQLPLUS_NLS_LANG= AMERICAN_AMERICA.ZHS16GBK.
For the Windows operating system, one more change is needed. Find # SQLPLUS_SYSTEMROOT=c:\\WINNT40 in the file. Remove the comment and change it to the operating system home directory of your machine. If the operating system's home directory is under Winnt on D disk, change it to SQLPLUS_SYSTEMROOT=d:\ WINNT.
The modification in the last item only applies to the Windows operating system, but not to the UNIX operating system. If this item is not modified in Windows, the following error will appear when connecting to the system in Oracle Enterprise Manager:
or
When the changes are complete, save the file and exit editing. Reconnect SQL PLUS Worksheet, the character set garble problem is resolved, and the correct simplified Chinese character set is displayed.