每次建立外部表的时候总会出这样那样的错，今天把过程记录下来。

oracle@linux-rpu7:~> mkdir dir
oracle@linux-rpu7:~> cd dir/
oracle@linux-rpu7:~/dir> ls
oracle@linux-rpu7:~/dir> more  /etc/group > test.txt
oracle@linux-rpu7:~/dir> ls
test.txt

创建目录并授权

SQL> create directory test_dir as '/opt/oracle/dir';

Directory created.

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

Grant succeeded.

建立外部表

SQL> create table test(
  2  name varchar2(20),
  3  passwd varchar2(20),
  4  groupid int, 
  5  user varchar2(20))
  6  organization external (
  7  type oracle_loader
  8  default directory test_dir
  9  access parameters (
 10  badfile 'bad.txt'
 11  logfile 'log.txt'
 12  fields terminated by ":"
 13  missing field values are null)
 14  location('test.txt'));
user varchar2(20))
*
ERROR at line 5:
ORA-00904: : invalid identifier    

SQL> list 5
  5* user varchar2(20))
SQL> change /user/username
  5* username varchar2(20))
SQL> /

Table created.

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "column, debug,
exit, fields, records"
KUP-01007: at line 1 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 19

parameters里面内容写错，少写了

删除重建表
SQL> drop table test purge;

Table dropped.
SQL> create table test(
  2  name varchar2(20),
  3  passwd varchar2(20),
  4  groupid int,
  5  username varchar2(20))
  6  orgnaization external (
  7  type oracle_loader
  8  default directory test_dir
  9  access parameters
 10  (records delimited by newline
 11  badfile 'bad.txt'
 12  logfile 'log.txt'
 13  fields terminated by ":"
 14  missing field values are null)
 15  location('test.txt'));
orgnaization external (
*
ERROR at line 6:
ORA-00922: missing or invalid option

粗心，写错
SQL> list 6
  6* orgnaization external (
SQL> change /orgnaization/organization
  6* organization external (
SQL> /

Table created.

SQL> select * from test;

NAME                 PASSWD                  GROUPID USERNAME
-------------------- -------------------- ---------- --------------------
root                 x                             0

基本算是成功。