In Linux, Oracle inserts (imports) Chinese records as question marks and Solutions

Character Set

Oracle official answers to character sets and nls_lang: http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm

What is character set first? Character Set is a collection of symbols interpreted by byte data. It has different sizes and relationships. For example, us7ascii is a subset of zhs16gbk,
There will be no data interpretation issues from us7ascii to zhs16gbk, and there will be no data loss. Oracle also requires that the export from the subset to the superset be supported, and vice versa. In
In some character sets, utf8 should be the largest, because it is based on Unicode and stores double-byte characters (so it occupies more space ),.

Second, once the database is created, the character set of the database cannot be changed. Therefore, it is very important to consider which character set to use at the beginning of design and installation. The database character set should be a superset of the local character set of the operating system. The character set used by the customers who access the database determines which superset to choose, that is, the database character set should be the superset of all customer character sets.

Nls_lang variable:

In Windows: you can find the settings in the system environment variables and the Registry.

Linux: set it in the. bash_profile file of the Oracle user.

Nls_lang format:
Nls_lang = language_territory.charset
There are three components (language, region, and Character Set), each of which controls the NLS subset features. Language indicates the language of the server message.
Territory specifies the date and number format of the server.
Charset
For example:
American _ America. zhs16gbk

Linux system language:

/Etc/sysconfig/i18n

What does each line in/etc/sysconfig/i18n under Red Hat mean?

Lang = "zh_cn.gb18030"

Supported = "zh_cn.gb18030: zh_cn: Zh: en_US.UTF-8: en_us: en"

Sysfont = "latarcyrheb-sun16"

/Etc/sysconfig/i18n the region language settings of the system are stored here, And i18n is the abbreviation of internationalization, Which is exactly 18 letters between I and N.

The first line indicates the language environment variable settings of your current system. Here is zh_cn.gb18030

The second line indicates that the system has preset language support and the language not in the project cannot be properly displayed.

The third line defines the console terminal font, the font displayed when you log on to text is the latarcyrheb-sun16

When importing and exporting Oracle Data, you must set the character set of the client and the Oracle server to be consistent. Otherwise, Chinese characters imported to the database may be garbled and cannot be correctly displayed, set the correct environment variables, and then set the language settings of the operating system, that is, set i18n. The following is an example of my experiment on the server:

Create Table Test

SQL> create table test(id number(2),name char(12));

Table created.

Insert the first record:

SQL> insert into test values (1, 'zhang san ');

1 row created.

Then View:

SQL> select * from test;

        ID NAME

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

         1 ????

Displayed in Chinese ???

View the character set of the database server on the server

SQL> select userenv('language') from dual;

Userenv ('language ')

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

American_america.zhs16gbk

If you want to insert or import data from a client, you must set the client's nls_lang variable to be consistent with the server. Here, you need to set the client settings Library:

AMERICAN_AMERICA.ZHS16GBK

View the nls_lang variable value of the Oracle server:

[oracle@oradb ~]$ echo $NLS_LANG

AMERICAN

Change the value of nls_name to american_america.zhs16gbk.

Then log on again and perform the query again:

SQL> select * from test;

        ID NAME

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

         1 ????

Or question mark, insert test:

SQL> insert into test values (2, 'Li si ');

1 row created.

SQL> select * from test;

        ID NAME

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

         1 ????

         2 ????

Check the i18n file:

vi /etc/sysconfig/i18n

LANG="en_US.UTF-8"

SUPPORTED="zh_CN.UTF-8:zh_CN:zh:en_US.UTF-8:en_US:en"

SYSFONT="latarcyrheb-sun16"

Modify the Lang value to the Chinese Character Set of the Library. The following is a complete i18n file setting on the Internet:
(Key)

LANG="zh_CN.gb2312"

SUPPORTED="zh_CN.GB18030:zh_CN:zh:zh_CN.GB2312:zh_CN:zh:zh_CN.gbk:zh_CN:zh:zh_HK.UTF-8:zh_HK:zh:zh_CN.UTF-8:zh_CN:zh:zh_TW.UTF-8:zh_TW:zh:en_US.UTF-8:en_US:en"

SYSFONT="latarcyrheb-sun16"

After modification, log on to Oracle again, and then query:

SQL> select * from test;
        ID NAME

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

         1 ????

         2 ????
Still cannot be correctly displayed. Perform the insert test now:
SQL> insert into test values (3, 'wang wu ');
1 row created
SQL> select * from test;
        SQL> select * from test;
ID name

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

1 ????

2 ????

3. Wang Wu

OK. Now we can see that the inserted record can be normally displayed, but the previous one still cannot be displayed. The inserted record cannot be correctly converted, even if it is repaired
Summary:
Three steps:
1: database-encoded character set (query: Select userenv ('language') from dual;
2: Modify the. bash_profile file in Oracle for the character set encoded by the client. (Operation above)
3: Display encoding Character Set of the system