Character Set Issues Affecting Oracle Chinese Character Display

---- There are many users who use ORACLE as a database management platform in large and medium-sized database management systems at home and abroad. ORACLE is beyond review in terms of both database management capabilities and security. However, it brings a lot of trouble to Chinese users in displaying Chinese characters. I have been engaged in ORACLE Database Management for many years, users in the surrounding area and users in other places often receive support letters that reflect Chinese character display problems in ORACLE databases. The main phenomenon is that Chinese characters are displayed as unidentifiable garbled characters, leading to the unavailability of a large amount of original information. This article will discuss the causes and solutions of this problem for your reference.

---- 1. Cause Analysis

---- Through the analysis of user feedback, it is found that improper Character Set setting is a key issue affecting Chinese character display in the ORACLE database. So how will the character set take a while? Character Set is set by ORACLE to adapt to text display in different languages. The character sets used for displaying Chinese characters include ZHS16CGB231280, US7ASCII, and WE8ISO8859P1. The character set must not only exist on the server, but also be registered on the client. On the server side, the character set is specified when ORACLE is installed. The character set registration information is stored in the V $ NLS_PARAMETERS table of the ORACLE database dictionary. on the client side, the character set is divided into two situations, one scenario is SQL * net 2.0 or earlier. The character set is in oracle under the windows System directory. the INI file is registered. In another case, SQL * net 2.0 or later (namely, 32-bit) versions are registered in the windows system registry. To correctly display Chinese characters in the ORACLE database on the client, the character set on the server must be consistent with that on the client. Secondly, the data Character Set loaded to the ORACLE database must be consistent with the character set specified on the server. Therefore, we classify user problems and generate the following reasons for Chinese Character Display exceptions:

---- 1. 1 the character set specified by the server is different from the customer character set, and is consistent with the character set loaded.

---- This is the most common case. You only need to set the character set of the client correctly. For the solution, see 2.1.

---- The character set specified by the server is the same as the customer character set and is inconsistent with the loaded data character set.

---- This type of problem generally occurs when the ORACLE version is upgraded or the system is reinstalled with a different character set than the original server, and the restored backup data is still detached from the original character set, and load the data that is detached from other ORACLE databases that use different character sets. In both cases, Chinese characters cannot be displayed regardless of whether the server and client character sets are consistent. For the solution, see 2.2.

---- 1.3 the character set specified by the server is different from the customer character set and is inconsistent with the input data character set.

---- In this case, Chinese characters are entered from the client when the character set on the client is inconsistent with that on the server. The entered information cannot display Chinese characters even if the client character set is changed correctly. For the solution, see 2.3.

---- 2. Solution

---- The solutions for the above three situations will be given below. For ease of description, assuming that the client uses the Windows 95/98 environment and has successfully configured the TCP/IP protocol, the SQL * net and SQL * pluse products of ORACLE are installed.

---- 2.1 set the client character set to be consistent with the server Character Set

---- Assume that the current server uses the US7ASCII character set.

---- (1) view server-side character sets

---- Log on to a valid ORACLE user through SQL * plus on the client or server, and execute the following SQL statement:

SQL> select * from V $ NLS_PARAMETERS
Parameter value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
.... ....
NLS_CHARACTERSET US7ASCII
NLS_SORT BINARY
NLS_NCHAR_CHARACTERSET US7ASCII
---- From the list above, we can see that the character set of the server-side ORACLE database is 'us7ascii '.

---- (2) configure the client according to the server Character Set

---- There are two configuration methods:

Specify
---- When installing the ORACLE client product software, select the character set consistent with that of the ORACLE server (in this example, US7ASCII.

How to modify Registration Information
---- The SQL * net version selected by the ORACLE client is divided into the following two situations:

---- A. The client is SQL * net 2.0 or earlier

---- Go to the Windows System directory, edit the oracle. ini file, replace the original character set with US7ASCII, and restart the computer. The settings take effect.

---- B. The client is SQL * net 2.0 or later.

---- Run REGEDIT in WIN98. Select HKEY_LOCAL_MACHINE in step 1, select SOFTWARE in step 2, select ORACLE in step 3, and select NLS_LANG in step 4, the word set (in this example, AMERICAN_AMERICAN.US7ASCII) that is the same as that of the server ).

---- 2.2 The forced loading data character set is consistent with the server-side character set

---- Assume that the character set for data to be loaded from the original ORACLE database is US7ASCII, and the current ORACLE Server character set is WE8ISO8859P1.

---- Three solutions are provided below:

---- (1) reinstall ORACLE on the server

---- When you reinstall ORACLE, select the same character set (in this example, US7ASCII) as the original unload data ).

---- Load the original detached data.

---- This situation only applies to empty databases and data with the same character set.

---- (2) Forcibly modify the current character set of ORACLE on the server

---- Before using the imp command to load data, log on to the system DBA using SQL * plus on the client and run the following SQL statement to modify the character set of the current ORACLE database:

SQL> create database character set US7ASCII
* Create database character set US7ASCII
ERROR at line 1:
ORA-01031: insufficient privileges
---- You will find that the above error message is prompted during statement execution. In this case, you don't need to worry about it. In fact, the character set of the ORACLE database has been forcibly changed to US7ASCII, and then load data using the imp command. After the data is loaded, shut down the database and start up the database. log on to the ORACLE database with a valid user and run select * from V $ NLS_PARAMETERS at the SQL> command prompt, you can see that the character set of the ORACLE database has been restored. When you view a table with Chinese character data, the Chinese character is correctly displayed.

---- (3) Use Data Format dump to avoid character set restrictions

---- This method is mainly used to load different character sets of external ORACLE databases. The method is as follows:

---- First load the data to the server with the same character set, then use the Conversion Tool to unload the database in foxbase format or access format, and then transfer it to the ORACLE database of different character sets, this avoids ORACLE Character Set troubles. Currently, there are many database format conversion tools, such as pipeline provided by power builder5.0 and later versions, and data import/export functions provided by Microsoft Access database. For more information about the conversion method, see ..

---- 2.3 replace Chinese characters with matching character sets

---- There is no good way to match the character set of the client and the server first, and then replace the Chinese character part with the original Chinese character pattern.