Exteernal table ORA-29913，ORA-30653，KUP-01005

1. create directory
$ sqlplus / as sysdba

SQL> create or replace directory DPUMPDIR  as '/home/oracle/dmp';

SQL> col owner for a5;
SQL> col directory_name for a15;
SQL> col directory_path for a20;

SQL> SELECT *  FROM  dba_directories where directory_name='DPUMPDIR';
OWNER DIRECTORY_NAME  DIRECTORY_PATH
----- --------------- ----------------
SYS   DPUMPDIR        /home/oracle/dmp

SQL>grant read ,write on directory  DPUMPDIR  to &user;

2.编辑数据文件emp.dat
 将emp.dat存放到emp.dat放到directory对应的目录下，即/home/oracle/dmp
 emp.dat 内容如下：
 
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7944,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30

3.创建外部表 create external_table
SQL> conn scott/tiger
Connected.

SQL> show user;
USER is "SCOTT"

SQL>  create table ext_emp
  2     (EMPNO NUMBER(4) ,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2) ,
  9      DEPTNO NUMBER(2)
 10     )
 11     ORGANIZATION EXTERNAL
 12     ( type oracle_loader
 13       default directory DPUMPDIR
 14       access parameters
 15       (
 16       fields terminated by ',' )
 17       location ('emp.dat')
 18     );
Table created 
  
SQL> select * from ext_emp;
select * from ext_emp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

在对应的directory目录下查看外部表对应的bad和log日志，发现加载的数据文件中有标题。
修改创建外部表的语句，在其中加入 skip=1 重新创建。(或者直接删除标题行也可以)

SQL>  create table ext_emp
  2     (EMPNO NUMBER(4) ,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2) ,
  9      DEPTNO NUMBER(2)
 10     )
 11     ORGANIZATION EXTERNAL
 12     ( type oracle_loader
 13       default directory DPUMPDIR
 14       access parameters
 15       (
 16       skip=1
 17       fields terminated by ',' )
 18       location ('emp.dat')
 19     );
 
Table created
SQL> select * from ext_emp;
select * from ext_emp
 
ORA-29913: 执行 ODCIEXTTABLEOPEN 调出时出错
ORA-29400: 数据插件错误KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "skip": expecting one of: "column, fields, records"
KUP-01007: at line 1 column 1
 
 
在创建外部表的语句中加入结束标记 records delimited by newline，
指定记录以换行符结束，重新创建。
SQL> drop table ext_emp;
Table dropped
 
SQL>  create table ext_emp
  2     (EMPNO NUMBER(4) ,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2) ,
  9      DEPTNO NUMBER(2)
 10     )
 11     ORGANIZATION EXTERNAL
 12     ( type oracle_loader
 13       default directory DPUMPDIR
 14       access parameters
 15       (
 16       records delimited by newline
 17       skip=1
 18       fields terminated by "," )
 19       location ('emp.dat')
 20     );
 
Table created

SQL> select count(1) from ext_emp;
 
  COUNT(1)
----------
        11