今天试用了外部表装载数据

创建外部表的脚本：

create table ext
(ID VARCHAR2(10),
PWD VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(type oracle_loader
default directory extir
access parameters
(records delimited by newline
skip 0
fields terminated by ","
(id,pwd)
)
location('test.ctl')
)

test.ctl内容:

LOAD DATA
INFILE *
INTO TABLE TEST
FIELDS TERMINATED BY ","
(ID,PWD)
BEGINDATA
JASON,KK
JJ,A
ASDF,DD

产生如下错误：

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

原因是没有忽略了ctl文件前6行不是数据，不符合格式

alter table ext access parameters后成功select外部表ext

SQL> select * from ext;

ID PWD
---------- ----------
JASON KK
JJ A
ASDF DD

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11717161/viewspace-1031230/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11717161/viewspace-1031230/