Oracle Chinese character display character set problem
---- In the large and medium-sized database management system at home and abroad, it is more than the user of Oracle as the database management platform. Oracle is unpleasable regardless of database management ability, but it is unpredictable in the display of Chinese characters. The author has brought a lot of trouble. The author has been engaged in Oracle database management, often receiving the surrounding users and field users Oracle Database Chinese Characters Display Problem Forward, the main phenomenon is to display Chinese characters as unrecognized garbled, resulting in a lot of information that cannot be used. This article will conduct some discussions on this problem and a solution for users who have problems in this area. ---- 1, Cause Analysis ---- By analyzing the user's reflection, the setting of the character set is improperly impacts the key issues displayed by the Oracle database Chinese characters. So what is the character set? The character set is Oracle to accommodate different language text display. The character set used for Chinese characters mainly has zHS16CGB231280, US7ASCII, WE8ISO8859P1, etc. The character set is not only in the server side, but also the client must also have a character set registration. The server side, the character set is specified when installing Oracle, the 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 cases, and the situation is SQL * NET 2.0 or less, The character set is registered in the Oracle.ini file in the system directory of Windows; the other is SQL * NET 2.0 or more (ie 32) versions, and the character set is registered in the system registry of Windows. To properly display the Oracle Database Chinese character information correctly, you must first match the character set of the server side to the client's character set; followed by the data character set loaded to the Oracle database, must be consistent with the server specified character set. Therefore, the problem exists in the classification, and the cause of the Chinese characters will have the following reasons: ---- 1. 1 The server specifies that the character set is different from the customer character set, and the load data character set is consistent. ---- This situation is the most common, as long as the client's character set is set correctly, the solution is shown in 2.1. ---- 1. 2 The server specifies that the character set is the same as the customer character set, which is inconsistent with the load data character set. ---- This type of problem generally occurs when the Oracle version is upgraded or reinstalled, and the character set of the original server is selected, while the restored backup data is still unloaded according to the original character set, and loaded from other Use the data of the Oracle database of different character sets. In both cases, Chinese characters cannot be displayed regardless of whether the server side and client character sets are consistent. Solution 2.2. ---- 1.3 The server specifies that the character set is different from the customer character set, which is inconsistent with the input data character set. ---- This situation is the Chinese character information from the client when the client is inconsistent with the server-side character set. This information is entered even if the client character set is changed correctly, and the Chinese characters cannot be displayed. Solution 2.3. ---- 2. Solution ---- The following will give a solution to the above three situations. For the convenience of narrative, it is assumed that the client uses the Windows95 / 98 environment and has successfully configured the TCP / IP protocol, and Oracle's SQL * NET, SQL * PLUSE product is installed. ---- 2.1 Set the client character set consistent with the server-side character set - Hypothesis The current server side uses the US7ASCIi character set.
---- (1) View server-side character set ---- SQL * Plus logged in to Oracle through the client or server-side SQL * Plus, perform the following SQL statement: SQL> SELECT * FROM VALUE NLS_PARAMETERS Parameter Value NLS_LANGUAGE AMERICAN NLS_TERRITORY America .... .... NLS_CHARACTERSET US7ASCII NLS_SORT BINARY NLS_NCHAR_CHARACTERSET US7ASCII ---- From the list information you can see that the server-side Oracle database is US7ASCII. ---- (2) Configuring the client in accordance with the server-side character set ---- Configuration method has two types: specified when installing Oracle client software ---- When you install Oracle's client product software, choose The character set consistent with the Oracle server (this example is USASCII). Method for modifying registration information ---- The version of the SQL * NET selected according to the Oracle client is divided into the following two situations: ---- a. Client is SQL * NET 2.0 below ---- Entering Windows Directory, edit the Oracle.ini file, replace the original character set with US7ASCII, restart your computer, set into force. ---- b. Client is SQL * NET 2.0 or higher ---- running regedit under Win98, first step in HKEY_MACAL_MACHINE, the second step Select Software, third step Select Oracle, fourth step Select NLS_LANG, type The same character set as the server side (this example is: American_american.us7ascii). ---- 2.2 Forced load data character sets match with server-side character set - Hally, it is necessary to load the data from the original Oracle database to US7ASCII, the current Oracle server character set is WE8ISO8859P1. ---- The following provides three solutions: ---- (1) The server-side reinstall Oracle ---- Select the character set consistent with the original unloading data when reinstalling Oracle (US7ASCII). ---- Add the data that is originally unloaded. ---- This situation is only used for empty spaces and data with the same character set. ---- (2) Forced to modify the server-side Oracle Current Character Set - Before loading the data with the Imp command, first log in to the System DBA user with SQL * Plus, perform the following SQL statement for the current Oracle database character set Modify: SQL> CREATE DATABASE Character Set US7ASCII * Create Database Character Set US7ASCII Error At Line 1: ORA-01031: Insufficient Privileges ---- You will find the above error prompt information during the execution of the statement, do not care, actually The character set of the Oracle database has been forced to modify to US7ASCII, and then load data with the IMP command. After the data load is complete, the shutdown database, then the Startup database, use the legal user to log in to the Oracle database, under the SQL> command prompt, run SELECT * from V $ nls_parameters, you can see that the Oracle database character set has been restored, then view again When there is a table with Chinese character data, Chinese characters can be displayed correctly.
