Analyze and solve the problem of garbled Chinese characters in Oracle under Linux

When using the Oracle database under the Linux system, the problem of garbled Chinese characters may occur, which is mainly caused by incorrect character set settings. This article will introduce how to solve the problem of garbled Chinese characters in Oracle database under Linux system.

1. Problem phenomenon

When using Oracle database under Linux system, the problem of garbled Chinese characters may occur. Garbled characters are usually displayed as some unrecognizable characters, or as placeholders such as squares or question marks.

2. Cause of the problem

The character set of Oracle database includes two aspects: database character set and client character set. When the client character set is inconsistent with the database character set, Chinese characters will be garbled. When installing the Oracle database under a Linux system, you need to set the correct character set, otherwise the problem of garbled Chinese characters will easily occur.

3. Solution

1. Query the character set of the database and client

We can use the following two commands to query the character set of the current database and client respectively:

select * from nls_database_parameters where parameter like 'NLS%CHARACTERSET';

The output is similar to:

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               ZHS16GBK

select * from nls_session_parameters where parameter like 'NLS%CHARACTERSET';

The output is similar to:

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               AL32UTF8

We can find that the character set of the current database is ZHS16GBK, and the client’s characters The set is AL32UTF8, which is the reason why Chinese characters are garbled.

2. Modify the client character set

We can solve the problem of garbled Chinese characters by modifying the client character set to keep it consistent with the database character set.

First we need to edit the client's tnsnames.ora file, which is located in the $ORACLE_HOME/network/admin directory. We can use the following command to open the file:

vi $ORACLE_HOME/network/admin/tnsnames.ora

Find the client’s connection information in the opened file, for example:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

Then add the following two lines to the connection information:

NLS_LANG = "ZHS16GBK"
NLS_NUMERIC_CHARACTERS = ",."

NLS_LANG Indicates the character set of the current client. This value determines the character set in which the client and server exchange data. NLS_NUMERIC_CHARACTERS represents the format of numeric characters, which is ., by default. This parameter needs to be modified in some special cases.

The final modified connection information may look like:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  (NLS_LANG = "ZHS16GBK")
  (NLS_NUMERIC_CHARACTERS = ",.")

Save the file and close.

3. Reconnect to the database

After modifying the client character set, we need to reconnect to the database to make it effective. You can use the following command to reconnect to the database:

sqlplus / as sysdba

Enter the password to connect to the database, and then query the client's character set again:

select * from nls_session_parameters where parameter like 'NLS%CHARACTERSET';

The output is similar to:

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               ZHS16GBK

You can see The character set sent to the client has been successfully modified to ZHS16GBK, thus solving the problem of garbled Chinese characters.

4. Summary

When using Oracle database under Linux system, garbled Chinese characters are a common problem. This is mainly due to the inconsistency between the client character set and the database character set. By modifying the character set of the client, we can solve the problem of garbled Chinese characters. The above are the specific steps to solve the problem of garbled Chinese characters in the Oracle database under Linux system.

The above is the detailed content of Analyze and solve the problem of garbled Chinese characters in Oracle under Linux. For more information, please follow other related articles on the PHP Chinese website!