How to solve the garbled character set in Oracle

oracle Solution to modify the garbled character set: 1. Find the TNSNAMES.ORA file, find the alias of the database to be connected, set the environment variable NLS_LANG, open SQL*Plus, and use the above configuration to connect to the database; 2. Create a table to store Chinese characters, insert some Chinese characters into the field that stores Chinese characters, and use the UTL_FILE package to output Chinese characters. After execution, a file named chinese.txt will be created in the UTL_DIR directory, which contains Chinese characters.

The operating environment of this tutorial: Windows 10 system, Oracle Database 20c version, DELL G3 computer.

In the Oracle database, garbled characters often occur when setting the Chinese character set, which causes headaches for many developers and administrators. Today, the editor has brought you some methods to solve the problem of Chinese garbled characters in Oracle database. I hope it will be helpful to you.

Method 1: Modify NLS_LANG

NLS_LANG is an environment variable in the Oracle database, which defines the encoding method of characters in the database. After Oracle 9i, the value of NLS_LANG defaults to AMERICAN_AMERICA.UTF8. This encoding method does not support Chinese, so we need to manually modify the value of NLS_LANG to support Chinese.

The steps are as follows:

1. Find the TNSNAMES.ORA file and find the alias of the database to be connected, such as:

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

2. Set the environment variable NLS_LANG, value The character set to be used, such as:

set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

or

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

3. Open SQL*Plus and use the above configuration to connect to the database:

sqlplus system/oracle@orcl

Method 2: Use UTL_FILE package

UTL_FILE package is a standard package used for file I/O operations in Oracle database. UTL_FILE can be used to read and write text files, and Chinese characters can be easily processed.

The steps are as follows:

1. Create a table that stores Chinese characters, such as:

CREATE TABLE chinese (
name VARCHAR2(20),
clob CLOB
);

2. Insert some Chinese characters into the field that stores Chinese characters:

INSERT INTO chinese VALUES (‘中国’, ‘中华人民共和国’);

3. Create an output file and use the UTL_FILE package to output Chinese characters:

DECLARE
file_handle UTL_FILE.FILE_TYPE;
chinese_clob CLOB;
BEGIN
SELECT clob INTO chinese_clob FROM chinese WHERE name=’中国’;
file_handle := UTL_FILE.FOPEN(‘UTL_DIR’, ‘chinese.txt’, ‘w’, 32767);
UTL_FILE.PUT_LINE(file_handle, chinese_clob, FALSE);
UTL_FILE.FCLOSE(file_handle);
END;

After execution, a file named chinese.txt will be created in the UTL_DIR directory, which contains Chinese characters.

Summary

Both of the above two methods can solve the problem of Chinese garbled characters in Oracle database. Method one is to modify the value of the NLS_LANG environment variable to support Chinese characters, and method two is to read and write Chinese characters through the UTL_FILE package. According to the specific needs, you can choose the corresponding method to solve it.

The above is the detailed content of How to solve the garbled character set in Oracle. For more information, please follow other related articles on the PHP Chinese website!