导入数据的时候，客户端NLS_LANG设置要谨慎
时间：2010-07-02 来源：TOMSYAN
很多时候我们的数据库采用的字符集是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 test/[email protected]/test
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 test/[email protected]/test 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 test/[email protected]/test 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 test/[email protected]/test
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
相关阅读 更多 +
