oracle 外部表及解决ora-29400,ora-29913错误

 对外部表的理解及测试，，，，如有理解不正确请大家指正

 

语法：

create table 表名(

列名1,列名2，......

)

organization external  ###说明创建外部表

(

 type 访问类型            ###一般是 ORACLE_LOADER ,ORACLE_DATAPUMP

 default directory   路径名   ####指定默认目录对象

 access parameter (        ###数据源文件与表中行之间的映射关系

           records delimited by 设置分隔符    ###一般是newline

           [badfile 路径名:'文件名']   ##存放错误日志的目录和文件名

           [logfile 路径名:'文件名']    ##日志的目录和文件名

           [discardfile 路径名：'文件名']  ###废弃文件存放的目录和文件名

          fields terminated by  '分隔符'   ###设置文件中字段的分隔符

          [ missing field values are null]   ##无值字段的处理

          [reject rows with all null fields]   ##无值行的处理

              ( 列名1, 列名2, .....)　　　

   )

location (路径名:'数据文件名')

)

[reject limit（或unlimited） 数值 ] ###设置多少行转换失败时返回oracle错误，默认为0

[parallel]

 

例子：

1.在系统下创建三个目录

/home/oracle/test_folder/data --数据文件目录

touch test.dat --数据文件

录入数据

1001,abc,2013/07/12,1000
1002,abcd,2014/01/31,1000
1003,bdc,2012/03/12,1020

/home/oracle/test_folder/log  --日志文件目录

touch test.log --日志文件

/home/oracle/test_folder/bad --错误文件目录

touch test.bad --错误文件

2.创建目录

登陆 conn  / as sysdba 下

create or replace directory DATA_DIR AS '/home/oracle/test_folder/data' ;  --数据文件目录
create or replace directory LOG_DIR AS '/home/oracle/test_folder/log' ;--日志文件目录
create or replace directory BAD_DIR AS '/home/oracle/test_folder/bad' ;--错误文件目录

3.授权

grant write ,read on directory DATA_DIR to scott;
grant write ,read on directory LOG_DIR to scott;
grant write ,read on directory BAD_DIR to scott;

3.创建表
###在scott 用户下
create table ext_emp
(
 emp_no varchar2(10),
 epm_name varchar2(20),
 hirte_date date,
 comm number
)
organization external
(
 type ORACLE_LOADER
 default directory DATA_DIR
 access parameters
 (
  records delimited by newline
  badfile BAD_DIR:'test.bad'
  logfile LOG_DIR:'test.log'
  fields terminated by ','
  (
   emp_no,
   epm_name,
   hirte_date char date_format date mask 'yyyy-mm-dd',
   comm
  )
 )
 location('test.dat')
)
parallel
reject limit unlimited;

 注意： 如果在access parameters 中  注释一些没有用的代码，系统认为这是不符合规则的语句，所以产生错误。

         原因是我在测试的时候注释一些内容，所以查询的时候一直报错。

         然后在网上找了报错的原因，一般都是说这是创建目录和授权及目录和文件访问权限或文件数据问题或语法引起的问题，

　　　然后一个一个排除是否是这些原因引起错误，后来发现都不是这些因素。

         最后重新建表才发现是注释引起的错误。。。。。（抓狂啊）

例如：

access parameters
 (
  records delimited by newline
  badfile DATA_DIR:'zlc.bad'
  logfile DATA_DIR:'zlc.log'
  fields terminated by ','

  -- missing field values are null  这两句可选的，
 -- reject rows with all null fields
  (
   emp_no,
   epm_name,
   hirte_date char date_format date mask 'yyyy-mm-dd',
   comm
  )
 )

如图：

 

然后查看 表结构发现 访问参数里面有注释 ，如图

 

---把上面的注释去掉问题就解决了。

select * from ext_emp