About Chinese character display garbled in Oracle database

1 Introduction

Oracle database as the industry's leading database products, in recent years in the domestic large and medium-sized enterprises have been widely used. Although the Oracle database product itself has been quite mature in localization, there are still a lot of users to respond to Chinese characters display garbled problem. For example, different users of the same database have different results for username queries in the same table:

"ORACLE??????" and "Oracle China Limited", it is obvious that the results will display Chinese characters as garbled, so why? The improper setting of character set is the key problem affecting the display of Chinese characters in Oracle database.

2 About character Sets

The character set is set by Oracle to accommodate different language and text displays. The character set used for Chinese character display mainly includes zhs16cgb231280, ZHS16GBK, Us7ascii and UTF-8. The character set exists both on the server side and on the client. The server-side 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, while the client character set is set in the system registry (Windows System) or in the user's environment variable (Unix system).

3 composition and setting of character set

The composition and setting of the character set are divided into two types of client and server:

(1) The composition and setting of the client character set. The client's character set is set by the current user's environment variable Nls_lang. Composition of environmental variable Nls_lang:

Nls_lang=language_territory.charset

which

Language the language of the specified server message

Territory specify the date and number format of the server

CharSet specified character set

Three components can be any combination, for example:

American_america. Us7scii

Simplified Chinese_china. Zhs16gbk

American_america. Zhs16gbk

The setting of the client character set is slightly different for different operating system settings: The Windows system is set in the registry key: Hkey_local_machinesoftwareoraclehome0nls_lang; UNIX systems are set in the current user's environment variables, such as adding a line to the current user's profile file as follows:

Nls_lang=simplified Chinese_china. Zhs16gbk;export Nls_lang

(2) The composition and setting of the service-end character set. The composition of the service-side character set is embodied in the Nls_language, Nls_territory, Nls_characterset Three values of the data dictionary table v$nls_parameters, where Nls_ The value of CharacterSet is the specific database character set.

such as using the query statement Sql>select * from V$nls_parameters;

The following results can be obtained:

PARAMETER VALUE

------------------------------------------------------------

Nls_language Simplified Chinese

Nls_territory

......

Nls_characterset ZHS16GBK

......

That is, the character set used by the current database is ZHS16GBK.

The character set of the database service side is set when the data is created. However, you can modify the set character set by using the following methods:

Method One: Rebuilding the database. Set the character set of the database to the desired character set when you set up the database.

Method Two: Modify the sys.props$ table. After the SYS user logs on to Oracle, the following statements are used to modify the corresponding character set and submit:

Sql>update props$ SET value$= ' ZHS16GBK '

WHERE name= ' Nls_characterset ';

sql>commit;

This method is used to change the database character set, which is only valid for changed data, that is, the original data in the database is still stored in the original character set.

In addition, some also use the CREATE DATABASE CHARACTER set ZHS16GBK command temporarily modify the character set, when the database is restarted, the database character set will revert to the original character set

4 Common character garbled problems and solutions

To correctly display the Chinese character information in the Oracle database on the client, you must first make the client's character set consistent with the server-side character set, and then the data character set to be loaded into the Oracle database must be consistent with the server character set. Accordingly, the problem of Chinese character display garbled can be divided into the following kinds of situations roughly:

(1) The client character set differs from the server-side character set, and the server-side character set is consistent with the load data character set. This is the most common case, just set the client's character set correctly. Specific solutions:

The first step: query V$nls_parameters get the character set of the server:

Sql>select * from V$nls_parameters;

The second step: set the client's character set according to the character set of the service end, and set the method to see the client's character set.

In the case of a UNIX system, the following two lines can be added to the current user's profile file:

Nls_lang=simplified Chinese_china. Zhs16gbk

Export Nls_lang

(2) The client character set is identical to the server-side character set, and the server-side character set is inconsistent with the load data character set. This typically occurs when the Oracle version is upgraded or the database is reinstalled with a different character set than the original database, and the backup data that is loaded is still being unloaded in the original character set. Another scenario is to load data that is unloaded from other Oracle databases that use different character sets. In both cases, the Chinese character is not displayed correctly, regardless of whether the client character set is consistent with the server-side character set. Specific solutions:

Scenario One: Modifying the service-side character set by the service-side character set is consistent with the load data character set, and then importing the data.

Scenario two: Using data format dumps to avoid problems with character sets. That is, the load data is dumped into a database that is consistent with its character set, then the data is either exported in text Format (with a small amount of data), or the data is poured through third-party tools such as Power Builder,access,foxpro, Finally, the inverted data is imported into the target database.

(3) The client character set differs from the server-side character set, and the service-side character set differs from the input data set. In this case, when the client character set 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. Solution: After modifying the client character set consistent with the service-side character set, re-enter the data.

which

Sqlplus garbled Solution

Export Nls_lang=american_america. ZHS16GBK (show English)

Or

Export nls_lang= "Simplified Chinese" _china.zhs16cgb231280

(Show Simplified Chinese)

5 concluding remarks

According to Oracle's official documentation, once the database is created, the character set of the database cannot be changed. Therefore, it is important to consider in advance which character set your database will choose. The general rule for database character set selection is to set the database character set as a superset of the operating system local character set, and the database character set should also be a superset of all customer character sets. If the same is the Chinese environment, in the choice of zhs16cgb231280 or ZHS16GBK, we are more of the case to choose ZHS16GBK, because it contains the zhs16cgb231280 character set.