建立目录:
create OR replace Directory ExtFile_data as '/sds/extab/tab/search/fidreb/';
create OR replace Directory ExtFile_log as '/sds/extab/tab/search/log/fidreb/';
create OR replace Directory ExtFile_bad as '/sds/extab/tab/search/bad/fidreb/';
grant read on Directory ExtFile_data to search;
grant read on Directory ExtFile_log to search;
grant write on Directory ExtFile_log to search;
grant read on Directory ExtFile_bad to search;
grant write on Directory ExtFile_bad to search;
建表:
drop table ext_kkuni_daily;
create table ext_kkuni_daily
(
mid varchar2(100),
ver varchar2(30),
thedate date
)
Organization External
(
type Oracle_Loader
default Directory ExtFile_data
Access parameters
(
records delimited by newline
badfile ExtFile_bad:'kkuni_%a_%p.bad'
logfile ExtFile_log:'kkuni_%a_%p.log'
fields terminated by x'06'
missing field values are null
(
mid char(100),
ver char(30),
thedate date 'dd/Mon/yyyy:hh24:mi:ss'
)
)
Location ('ext_kkuni_daily')
)
Parallel
Reject limit Unlimited;
外部表的定义关键是Oracle Loader参数,Records关键字后定义如果识别数据行,Fields关键字后定义如果识别字段,常用的如下:
Records:
DELIMITED BY 'XXX' —— 换行符,常用newline定义换行,如果文件中使用了特别的字符就要另外定义了;如果是特殊符号,可以使用OX'十六位值',例如tab(/t)的十六位是9,那么就是DELIMITED BY 0X'09';cr(/r)的十六位是d,那么就是DELIMITED BY 0X'0D'。
SKIP X —— 跳过X行数据,有些文件中第一行是列名,需要跳过第一行,则使用SKIP 1。
Fields:
TERMINATED BY 'x' —— 字段分割符。
ENCLOSED BY 'x' —— 字段引用符,包含在此符号内的数据都当成一个字段。例如一行数据格式如:"abc","a""b,""c,"。使用参数TERMINATED BY ',' ENCLOSED BY '"'后,系统会读到两个字段,第一个字段的值是abc,第二个字段值是a"b,"c,。
LRTRIM —— 删除空白字符。
MISSING FIELD VALUES ARE NULL —— 空缺值都设为Null。