最近一个客户遇到了用外部表加载数据中文乱码情况，经分析是ORACLE外部表的字符集设置错误导致的，以下是模拟该问题的实验。

以下是在window上操作系统数据库字符集为ZHS16GBK的环境做的实验：

1. 创建数据文件test_1.txt，数据如下：

1,XX信息技术有限公司,XX重要客户
2,XX股份有限公司,XX重要客户
3,XX发展股份有限公司,XX重要客户

2. 查看数据库当前字符集

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK

3. 使用SYS用户创建DIRECTORY对象并授权给SCOTT用户

SQL> conn / as sysdba

已连接。

SQL> create directory imp as 'd:';

目录已创建。

SQL> grant read,write on directory imp to scott;

授权成功。

4. 使用SCOTT用户创建字符集为UTF8的外部表

SQL> conn scott/tiger

已连接。

SQL> create table ext_test
2  (id number,
3  title varchar2(20),
4  subject varchar2(20))
5  organization external
6  (type oracle_loader
7  default directory imp
8  access parameters
9  (records delimited by newline characterset utf8
10  fields terminated by ","
11  (id,title,subject)
12  )
13  location('test_1.txt')
14  );

表已创建。

5. 此时查询数据中文就会乱码

SQL> select * from ext_test;

ID TITLE                SUBJECT
---------- -------------------- -----------------
1 XX？？??？？？??？   XX？？?？?
2 XX?？？？？?？       XX？？?？?
3 XX??？?？？？？?？   XX？？?？?

6. 修改外部表的字符集参数

SQL> alter table ext_test
2  access parameters
3  (records delimited by newline characterset zhs16gbk
4  fields terminated by ","
5  (id,title,subject)
6  );

表已更改。

7. 再次查询，就不会乱码

SQL> select * from ext_test;

ID TITLE                SUBJECT
---------- -------------------- --------------------
1 XX信息技术有限公司   XX重要客户
2 XX股份有限公司       XX重要客户
3 XX发展股份有限公司   XX重要客户

以下是在linux操作系统数据库字符集为ZHS16GBK的环境做的实验：

1. 连接linux数据库

C:Usersstream>sqlplus scott/tiger@192.168.249.98/dbdream
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 27 13:51:33 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning option

2. 查看数据库当前字符集

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               ZHS16GBK

3. 使用SCOTT用户创建字符集为UTF8的外部表

SQL> conn scott/tiger

已连接。

SQL> create table ext_test
2  (id number,
3  title varchar2(20),
4  subject varchar2(20))
5  organization external
6  (type oracle_loader
7  default directory imp
8  access parameters
9  (records delimited by newline characterset utf8
10  fields terminated by ","
11  (id,title,subject)
12  )
13  location('test_1.txt')
14  );

表已创建。

4. 此时查询数据中文不会乱码

SQL> select * from ext_test;

ID TITLE                SUBJECT
---------- ------------------- --------------------
1 XX信息技术有限公司   XX重要客户
2 XX股份有限公司       XX重要客户
3 XX发展股份有限公司   XX重要客户

5. 修改外部表的字符集参数

SQL> alter table ext_test
2  access parameters
3  (records delimited by newline characterset zhs16gbk
4  fields terminated by ","
5  (id,title,subject)
6  );

表已更改。

6. 查询报错

SQL> select * from ext_test;

select * from ext_test
*
第 1 行出现错误:
ORA-29913: 执行 ODCIEXTTABLEFETCH 调出时出错
ORA-30653: 已达到拒绝限制值

这也说明外部表的字符集需要和操作系统的字库一致，而不是和数据库字符集一致。

本文固定链接: http://www.dbdream.com.cn/2012/12/oracle%e5%a4%96%e9%83%a8%e8%a1%a8%e5%ad%97%e7%ac%a6%e9%9b%86%e8%ae%be%e7%bd%ae%e9%94%99%e8%af%af%e5%af%bc%e8%87%b4%e4%b8%ad%e6%96%87%e4%b9%b1%e7%a0%81/ | 信春哥，系统稳，闭眼上线不回滚！