导入数据的时候，客户端NLS_LANG设置要谨慎

很多时候我们的数据库采用的字符集是UTF8，而客户端大部分采用的是GBK。
有时候为了避免转码，可能需要设置NLS_LANG和服务端一致，但这样可能会导致一些汉字导入不进去。

SQL> SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

VALUE
----------------------------------------
AL32UTF8

当前的数据库采用的字符集是AL32UTF8的。

C:\>CHCP
活动的代码页: 936

可以查询一下微软的字符集编码 936对应的是GBK.

这个时候导入时没有问题的：

C:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 7月 2 11:02:08 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> DROP TABLE TEST PURGE;

表已删除。

SQL> CREATE TABLE TEST(COL1 CHAR(4));

表已创建。

SQL> HOST
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\>TYPE TEST.CTL
LOAD DATA
INFILE *
INTO TABLE TEST
TRUNCATE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(COL1)
BEGINDATA
型
钢
拱
架
及
其
他
初
期
支
护
的
所
有
人
工
费
后
点
C:\>sqlldr control=test.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期五 7月 2 11:05:22 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

达到提交点 - 逻辑记录计数 18
达到提交点 - 逻辑记录计数 19

C:\>EXIT

SQL> SELECT * FROM TEST;

COL1
--------
型
钢
拱
架
及
其
他
初
期
支
护

COL1
--------
的
所
有
人
工
费
后
点

已选择19行。

 

设置一下NLS_LANG和数据库服务器一致：

C:\>SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8

C:\>sqlldr control=test.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jul 2 11:08:34 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 18

C:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 2 11:08:40 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining Scoring Engine
and Real Application Testing options

SQL> SELECT * FROM TEST;

COL1
----
型
拱
及
其
他
初
期
支
护
所
有

COL1
----
人
工

13 rows selected.

可以看到有几个汉字没有被导入。

sqlldr日志文件中的内容如下：
SQL*Loader: Release 11.2.0.1.0 - Production on Fri Jul 2 11:08:34 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   test.ctl
Data File:      test.ctl
  Bad File:     test.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table TEST, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
COL1                                FIRST     *   ,       CHARACTER           

Record 2: Rejected - Error on table TEST, column COL1.
Multibyte character error.
Record 4: Rejected - Error on table TEST, column COL1.
Multibyte character error.
Record 12: Rejected - Error on table TEST, column COL1.
Multibyte character error.
Record 17: Rejected - Error on table TEST, column COL1.
Multibyte character error.
Record 18: Rejected - Error on table TEST, column COL1.
Multibyte character error.
Record 19: Rejected - Error on table TEST, column COL1.
Multibyte character error.

Table TEST:
  13 Rows successfully loaded.
  6 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  16512 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            19
Total logical records rejected:         6
Total logical records discarded:        0

Run began on Fri Jul 02 11:08:34 2010
Run ended on Fri Jul 02 11:08:34 2010

Elapsed time was:     00:00:00.25
CPU time was:         00:00:00.04