Character set problems affecting the display of Oracle Chinese characters

In the large and Medium-sized database management system at home and abroad, there are more users of Oracle as database management platform. Oracle is not only a database management capability or security is justifiable, but it is in the display of Chinese character information to bring a lot of trouble to China's users, the author for many years engaged in Oracle database management, Often received by the surrounding users and foreign users to reflect the Oracle database of Chinese character display problems, the main phenomenon is to display Chinese characters as unrecognizable garbled, resulting in a lot of information can not be used. This paper will discuss the causes and solutions of this problem, which can be used as reference for users who have problems in this field.

----1, cause analysis

----Through the analysis of user's reflection, it is found that improper setting of character set is the key problem affecting the display of Chinese characters in Oracle database. So how does a character set work? The character set is set by Oracle to accommodate different language and text displays. The character set used for Chinese character display is mainly zhs16cgb231280,us7ascii,we8iso8859p1 and so on. Character sets need to exist not only on the server side, but also on the client. Server side, the character set is specified when Oracle is installed, and character set registration information is stored in the V$nls_parameters table of the Oracle database dictionary; The client, the character set is divided into two situations, one of which is the Sql*net 2.0 version, The character set is registered in the Oracle.ini file in the Windows system directory, and the other is Sql*net 2.0 (32-bit), and the character set is registered in the Windows system registry. To correctly display the Oracle database kanji information on the client, you must first make the server-side character set consistent with the client's character set, and then the data set that is loaded into the Oracle database must be consistent with the server's specified character set. Therefore, the user's existing problems inductive classification, resulting in Chinese characters to show the reasons for the exception is roughly the following:

----1. The 1 server specifies a character set that is different from the client character set and is consistent with the load data character set.

----This situation is the most common, as long as the client character set correctly, the solution to see 2.1.

----1. The 2 server specifies the same character set as the client character set, inconsistent with the load data character set.

----Such problems typically occur when the Oracle version upgrades or reinstall the system with a different set of characters than the original server side, while the backed-up backup data is still being unloaded in the original character set and loading data from other Oracle databases that use different character sets. In both cases, the Chinese character cannot be displayed, regardless of whether the server-side and the client character set are consistent. The solution is shown in 2.2.

----1.3 server Specifies that the character set differs from the client character set and is inconsistent with the input data set.

----This situation is that when the client is inconsistent with the server-side character set, the Chinese character information is entered from the client. This information is entered even if the client character set is changed correctly, the kanji cannot be displayed. The solution is shown in 2.3.

----2. Solutions

----below will provide a solution to each of the above three scenarios. For the sake of narration, it is assumed that the client uses the WINDOWS95/98 environment and has successfully configured the TCP/IP protocol to install the Oracle Sql*net,sql*pluse product.

----2.1 Set the client character set to match the server-side character set

----assumes that the US7ASCII character set is used on the current server side.

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

----Log on to a legitimate user of Oracle through the client or server-side Sql*plus, 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 information above, you can see that the character set of the server-side Oracle database is ' US7ASCII '.

----(2) Configuring the client according to the server-side character set

There are two ways to----configuration:

Specify when Oracle's client software is installed
----When installing Oracle's client product software, choose a character set that is consistent with the Oracle server (in this case, US7ASCII).

Ways to modify registration information
----is divided into the following two scenarios based on the version of Sql*net selected by the Oracle client:

----A. Client is the Sql*net 2.0 version below

----Enter the system directory of Windows, edit the Oracle.ini file, replace the original character set with US7ASCII, restart the computer, and set the entry into effect.

----B. Client is Sql*net 2.0 version

----Run regedit under WIN98, the first step is HKEY_LOCAL_MACHINE, the second chooses software, the third selects ORACLE, the fourth step selects Nls_lang, type the same character set as the server side (in this case: American_american. US7ASCII).

----2.2 Forced load data character set consistent with server-side character set

----assume that the character set to load data from the original Oracle database is US7ASCII, and the current Oracle server character set is WE8ISO8859P1.

There are three solutions available----:

----(1) server-side Reinstall Oracle

----Choose a character set that is consistent with the original unload data when you reinstall Oracle (US7ASCII).

----Load the original unloaded data.

----This situation is used only for empty libraries and data that have the same character set.

----(2) to forcibly modify the server-side Oracle current character set

----before loading data with the IMP command, first log on to the System DBA user with Sql*plus on the client and execute the following SQL statements for the current Oracle database character Set modification:

SQL > CREATE DATABASE Character Set Us7ascii
* CREATE DATABASE Character Set Us7ascii
ERROR at line 1:
Ora-01031:insufficient Privileges
----You will find this error message in the execution of the statement, no matter what, in fact, the character set of the Oracle database has been forcibly modified to US7ASCII, then the data is loaded with imp command. After data loading is complete, shutdown database, then startup database, log into Oracle database with legitimate users, run SELECT * from v$nls_parameters at sql> command prompt, You can see that the Oracle database character set has been restored, and then when you look at the table with the Kanji character data, the Chinese characters are displayed correctly.

----(3) Dump with data format, avoid character set restrictions

----This method is primarily used to load different character set data for a foreign Oracle database. The method is as follows:

----to load data onto a server with the same character set, and then unload it into a foxbase format or access format database using the conversion tool, and then transfer it to an Oracle database in a different character set, thus avoiding the problem of Oracle character sets. There are many tools for database format conversion, such as the data import/export function provided by the Pipeline,microsoft Access database provided by the Power builder5.0 version. The conversion method is shown in the information note.

----2.3 Matching character sets to replace Chinese characters

----for the 1.3 mentioned situation, there is no good way, can only first match the client and server-side character set matching, according to the original input characters to replace the characters part of the character