文本文件sanguo.txt中有一组关于三国武将能力的数据：

吕布,体力,99,武力,99,智力,17
赵云,体力,98,武力,98,智力,86
典韦,体力,97,武力,96,智力,26
关羽,体力,97,武力,97,智力,97
马超,体力,97,武力,97,智力,52
张飞,体力,99,武力,98,智力,20
曹操,体力,82,武力,84,智力,89
刘备,体力,78,武力,71,智力,65
孙权,体力,82,武力,83,智力,88
诸葛亮,体力,71,武力,62,智力,99

[@more@]一、external table

1.必要权限

SQL> sho user
USER is "SYS"
SQL> grant create any directory to ggyy;

Grant succeeded.


2.创建directory

SQL> sho user
USER is "GGYY"
SQL> create directory testdir as '/opt/oracle/test';

Directory created.


3.创建外部表

SQL> create table sanguo
(
name varchar2(10),
attr1 varchar2(10),
val1 integer,
attr2 varchar2(10),
val2 integer,
attr3 varchar2(10),
val3 integer
)
organization external
(
default directory testdir
access parameters (fields terminated by ',')
location ('sanguo.txt')
); 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

Table created.

4.查询外部表

SQL> select * from sanguo;
select * from sanguo
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52



日志文件中报如下错误：

KUP-04021: field formatting error for field VAL3
KUP-04023: field start is after end of record
KUP-04101: record 10 rejected in file /opt/oracle/test/sanguo.txt

是诸葛亮的那条记录出了问题，在sanguo.txt中的“诸葛亮”后面加个空格，问题解决。

SQL> select * from sanguo;

NAME ATTR1 VAL1 ATTR2 VAL2 ATTR3 VAL3
---------- ---------- ---------- ---------- ---------- ---------- ----------
吕布 体力 99 武力 99 智力 17
赵云 体力 98 武力 98 智力 86
典韦 体力 97 武力 96 智力 26
关羽 体力 97 武力 97 智力 97
马超 体力 97 武力 97 智力 52
张飞 体力 99 武力 98 智力 20
曹操 体力 82 武力 84 智力 89
刘备 体力 78 武力 71 智力 65
孙权 体力 82 武力 83 智力 88
诸葛亮 体力 71 武力 62 智力 99

10 rows selected.


二、pivoting insert

sanguo表中的数据并不符合关系模型，可以通过pivoting insert的方式进行转换。

SQL> create table sanguo_new
2 (
3 name varchar(10),
4 attr varchar(10),
5 val integer
6 );

Table created.

SQL> insert all
2 into sanguo_new values(name, attr1, val1)
3 into sanguo_new values(name, attr2, val2)
4 into sanguo_new values(name, attr3, val3)
5 select name, attr1, val1, attr2, val2, attr3, val3 from sanguo;

30 rows created.


验证转换是否成功，查看sanguo_new表按name和attr排序后中间的10条记录：

SQL> select * from
(
select t.*, rownum as rn from
(select * from sanguo_new order by name, attr) t
where rownum < 21
)
where rn > 10; 2 3 4 5 6 7

NAME ATTR VAL RN
---------- ---------- ---------- ----------
吕布 智力 17 11
吕布 武力 99 12
孙权 体力 82 13
孙权 智力 88 14
孙权 武力 83 15
张飞 体力 99 16
张飞 智力 20 17
张飞 武力 98 18
曹操 体力 82 19
曹操 智力 89 20

10 rows selected.