1. Check the character set when the Oracle database is created:
Oracle Server execution
SQL> select name, value $ from sys. props $ where name like 'nls % ';
Name value $
----------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH. MI. SSXFF AM
Name value $
----------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH. MI. SSXFF AM
NLS_TIME_TZ_FORMAT HH. MI. SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH. MI. SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0
20 rows selected.
Confirm that the character set selected when the database was created is a UTF-8
. Execute SQL: select userenv ('language ') from dual; get oracle Server character set X
SQL> select userenv ('language') from dual;
USERENV ('language ')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.UTF8
2. Check the settings of NLS_LANG on the client that executes sqlldr.
Oracle client execution
[Oracle @ localhost hx] $ echo $ NLS_LANG
AMERICAN_AMERICA.UTF8
3. Try to make the character set settings checked in the preceding three steps consistent. Start to import text through sqlldr and check the results.
There are three possible causes:
1. the character set specified by the server is different from the customer character set, and is consistent with the loaded data character set.
Solution: in this case, you only need to set the client character set to be consistent with the server character set.
Oracle10g UTF8 encoding: AMERICAN_AMERICA.AL32UTF8
GBK encoding: SIMPLIFIED CHINESE_CHINA.ZHS16GBK
The client is changed to Chinese: Set the environment variable on the linux terminal
1. LANG = zh_CN
2. NLS_LANG = zhs16gbk
2. the character set specified by the server is the same as the customer character set, which is inconsistent with the loaded data character set.
Solution: forcibly load the data character set to be consistent with the server-side character set.
3. the character set specified by the server is different from the customer character set and is inconsistent with the input data character set.
There is no good solution to this situation so far.
Sometimes you can try to use iconv-f UTF-8-t gbk filename
Convert from the original character set UTF-8 to gbk
How to modify the character set of a database:
1. Set and modify server-side character sets:
1.1 select the correct character set when creating a database (the main character sets for displaying Chinese characters include ZHS16CGB231280, US7ASCII, WE8ISO8859P1, and ZHS16GBK)
1.2 If no character set is selected during creation, the character set uses the default character set. You can use the following command to view the character set of the database. log on to the database using a valid oracle user. select * from V $ NLS_PARAMETERS.
SQL> select * from V $ NLS_PARAMETERS
Parameter value
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
.... ....
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_NCHAR_CHARACTERSET WE8ISO8859P1
---- From the above information, we can see that the character set of the ORACLE database is 'we8iso8859p1 '.
You can use the update command to modify the character set of the database. However, modifying the character set may damage the original data. You must back up the database before modification. The command is as follows:
User sys logs on to oracle AS sysdba. String 9
SQL> update props $ set value $ = 'zhs16gbk' where name = 'nls _ characterset'
If the character set of the client is different from that of the server, you must modify the character set. Otherwise, Chinese characters cannot be correctly displayed. We recommend that you modify the character set of the client.
1.3 Data Import between databases with different character sets
A) reinstall the database or use the update command to modify the character set.
B) forcibly modify the current character set of the oracle database. Log on to oracle AS sysdba
SQL> create database character set ZHS16GBK (note: the character set here is the character set of the exported data)
* Create database character set ZHS16GBK
ERROR at line 1:
ORA-01031: insufficient privileges
Ignore this error and use imp to load data. After the data is loaded, restart oracle data and select * from V $ NLS_PARAMETERS. Then, you will find that the database character set is returned to the original character set.
C) use a third-party tool to bypass character sets. Such as powerbuild pipeline, delphi datadump, MS access data import and export tools.
