Oracle Character Set

----in large and medium-sized database management system, Oracle as a database management platform users more. Oracle database management capabilities and security are beyond reproach, but it is in the display of Chinese character information really brings a lot of trouble to Chinese users, I have been engaged in Oracle database management for many years, Often received by the surrounding users and field users to reflect on the Oracle database Chinese character display problems, the main phenomenon is to display Chinese characters as unrecognized garbled, resulting in a large amount of information can not be used. This paper will discuss the causes and solutions of this problem, for the reference of users who have problems in this area. ----1, cause analysis ----Through the analysis of the user's reflection, it is found that the improper setting of the character set is the key problem affecting the Chinese character display of Oracle database. So what about the character set? The character set is set by Oracle to fit the text display in different languages. The character set used for Chinese character display mainly has ZHS16CGB231280,US7ASCII,WE8ISO8859P1 and so on. Character sets need to be present not only on the server side, but also in the character set registration. 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, the client, the character set in two cases, one case is Sql*net 2.0 or less, The character set is registered in the Oracle.ini file in the system directory of Windows, and the other is Sql*net 2.0 (or 32-bit) version, which 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, followed by the data character set that is loaded into the Oracle database must match the server-specified character set. Therefore, the problem of user-induced classification, the emergence of Chinese characters display anomalies in the following ways: ----1. 1 server specifies that the character set is different from the customer character set and is consistent with the load data character set. ----This situation is the most common, as long as the client's character set is correct, the workaround is shown in 2.1. ----1. 2 server Specifies the same character set as the customer character set, inconsistent with the load data character set. ----This type of problem typically occurs when an Oracle version upgrade or reinstallation of the system selects a different character set than the original server side, while the restored loaded backup data is still unloaded from the original character set, and the load is unloaded from other Oracle databases that use different character sets. In both cases, the kanji cannot be displayed regardless of whether the server side and client character sets are consistent. The solution is shown in 2.2. ----1.3 server Specifies that the character set is different from the customer character set and is inconsistent with the input data character set. ----This is the case when the client and the server-side character set are inconsistent, the Chinese character information is entered from the client. This information entered does not display the Chinese character even if the client character set is changed correctly. The solution is shown in 2.3. ----2. Solutions ----The following will give a solution to each of the three scenarios listed below. For the sake of narrative convenience, assume that the client uses the WINDOWS95/98 environment and has successfully configured the TCP/IP protocol to install Oracle's Sql*net,sql*pluse product. ----2.1 Setting the client character set to match the server-side character set ----Assume that the current server side uses the US7ASCII character set. ----(1) View server-side character set ----Log on to a legitimate user of Oracle through a 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 ----The character set for the server-side Oracle database is ' Us7ascii ', as shown in the list information above. ----(2) Configure the client according to the server-side character set There are two ways to configure----: Specify when installing Oracle's client software ----When installing Oracle's client product software, select a character set that is consistent with the Oracle server (in this case, US7ASCII). Ways to modify registration information The----is divided into the following two scenarios based on the version of Sql*net selected by the Oracle client: ----A. The client is Sql*net version 2.0 ----Enter the system directory of Windows, edit the Oracle.ini file, replace the original character set with US7ASCII, restart the computer, and the settings take effect. ----B. Client is Sql*net 2.0 or later ----Run regedit under WIN98, the first step is to choose HKEY_LOCAL_MACHINE, the second step is to select Software, the third step is to select ORACLE, and the fourth step is to select Nls_lang and type the same character set as the server side (in this case: American_american. US7ASCII). ----2.2 Force load data character set consistent with server-side character set ----assume that the character set for loading data from the original Oracle database is US7ASCII, and that the current Oracle server character set is WE8ISO8859P1. ----The following three solutions are available: ----(1) server-side re-installation of Oracle ----Select the character set that is consistent with the original unload data when reinstalling Oracle (in this case, US7ASCII). ----Load the original unloaded data. ----This situation only applies to empty libraries and data with the same character set. ----(2) forcibly modifies the server-side Oracle current character set ----Log on to the System DBA user with Sql*plus on the client before loading the data with the IMP command, execute the following SQL statement 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 notice that the above error message occurred during the execution of the statement, and that the character set of the Oracle database was forcibly modified to Us7ascii, and then the data was loaded with the IMP command. After the data is loaded, shutdown the database, then startup the database, log in to the Oracle database with a legitimate user, and at the sql> command prompt, run select * from V$nls_parameters, You can see that the Oracle database character set has been restored, and when you look at a table with Kanji character data, the kanji are displayed correctly. ----(3) Using data format dumps to 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: ----load the data onto a server with the same character set, then use the conversion tool to unload the database into the Foxbase format or access format, and then transfer the conversion tool to an Oracle database of different character sets, thus avoiding the problem with the Oracle character set. There are many tools for database format conversion, such as the data import/export functionality provided by the Pipeline,microsoft Access database provided by the power builder5.0 version above. The conversion method is described in the relevant data sheet. ----2.3 Matching character set to replace Chinese characters ----for the situation mentioned in 1.3, there is no good way, only the client and the server-side character set to match the first, according to the original input character code to replace the Chinese character part.