外部表笔记一loader

-- 外部表如何创建?
create table ... organization external ... ;

在organization external子句中,可以指定以下内容:
TYPE
DEFAULT DIRECTORY
ACCESS PARAMETERS
LOCATION

create table emp_load(
...
)
organization external
(
type oracle_loader
default directory data_dir
access parameters (
...
)
location ('emp.dat')
);

较简单的子句如下:
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tab_dir
ACCESS PARAMETERS (FIELDS TERMINATED BY ',')
LOCATION ('info.dat')
);

[@more@]

-- 数据文件和日志输出文件的位置
都要使用数据库中的目录
Create directory data_dir as 'c:...bdump';
grant read,write on directory data_dir to scott;
目录用在以下地方:
Default directory 子句中
location 子句中, 文件名称形式为directory:file 可选项,如果不加,使用default directory
access parameters子句的输出文件参数中,形式为 directory:file

示例一
info.dat 文件内容
56november, 15, 1980 baker mary alice 09/01/2004
87december, 20, 1970 roper lisa marie 01/01/1999

SQL> CREATE TABLE emp2 (
emp_no CHAR(6),
last_name CHAR(25),
first_name CHAR(20),
middle_initial CHAR(1),
hire_date DATE,
dob DATE);

create table emp_load
(employee_number char(5),
employee_dob char(20),
employee_last_name char(20),
employee_first_name CHAR(15),
employee_middle_name CHAR(15),
employee_hire_date DATE)
organization external
(
type oracle_loader
default directory data_dir
access parameters(
records delimited by newline
fields (employee_number CHAR(2),
employee_dob CHAR(20),
employee_last_name CHAR(18),
employee_first_name CHAR(11),
employee_middle_name CHAR(11),
employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy"
)
)
location ('info.dat')
);

INSERT INTO emp2 (emp_no,
first_name,
middle_initial,
last_name,
hire_date,
dob)
(SELECT employee_number,
employee_first_name,
substr(employee_middle_name, 1, 1),
employee_last_name,
employee_hire_date,
to_date(employee_dob,'month, dd, yyyy')
FROM emp_load);

示例二:
prod_on_hand.dat
5013,1005,7,Atlanta,06-09-2011,
5013,1005,7,Atlanta,06-09-2011,
5013,1005,7,Atlanta,06-09-2011,
5013,1005,7,Atlanta,06-09-2011,

drop table t3;
create table t3(
onhandid number(7),
prodid number(7),
qty number(7),
city varchar2(30),
upddate date
)
organization external
(
type oracle_loader
default directory data_dir
access parameters(
records delimited by newline
skip 1
fields terminated by ',' (
onhandid char(12),
prodid char(12),
qty char(12),
city char(12),
upddate char(12) date_format date mask 'DD-MM-YYYY'
)
)
location ( 'prod_on_hand.dat' )
)
reject limit unlimited;

--复杂一点儿，加上日志等
drop table t3;
create table t3(
onhandid number(7),
prodid number(7),
qty number(7),
city varchar2(30),
upddate date
)
organization external
(
type oracle_loader
default directory data_dir
access parameters(
records delimited by newline
badfile 'DATA_DIR':'1.bad'
logfile 'DATA_DIR':'1.log'
readsize 1048576
skip 1
fields terminated by ','
ldrtrim
reject rows with all null fields
(
onhandid char(12),
prodid char(12),
qty char(12),
city char(12),
upddate char(12) date_format date mask 'DD-MM-YYYY'
)
)
location ( 'prod_on_hand.dat' )
)
reject limit unlimited;
select count(1) from t3;

-- badfile data_dir:'a.bad'
drop table t3;
create table t3(
onhandid number(7),
prodid number(7),
qty number(7),
city varchar2(30),
upddate date
)
organization external
(
type oracle_loader
default directory data_dir
access parameters(
records delimited by newline
badfile data_dir:'1.bad'
logfile data_dir:'1.log'
readsize 1048576
skip 1
fields terminated by ','
ldrtrim
reject rows with all null fields --此处不能只留下fields
(
onhandid char(12),
prodid char(12),
qty char(12),
city char(12),
upddate char(12) date_format date mask 'DD-MM-YYYY'
)
)
location ( 'prod_on_hand.dat' )
)
reject limit unlimited;
select count(1) from t3;
但不知道为什么,dat文件中将06-09-2011 换成06-SEP-2011 fields参数换成date mask 'DD-MON-YYYY' 就不识别

Access_parameters 子句
access_parameters子句中，按先后顺序包括四类内容：
comments
record_format_info
field_definitions
column_transforms

第一部分 注释部分 comments部分直接以--开头

第二部分 记录定义部分 record_format_info部分，按照记录的识别方式分为三类：fixedvariabledelimited-by
定长的示例
info.dat
Alvin Tolliver1976
KennethBaer 1963
Mary Dube 1973
......
ACCESS PARAMETERS (
RECORDS FIXED 20 FIELDS
( first_name CHAR(7),last_name CHAR(8),year_of_birth CHAR(4) )
)
LOCATION ('info.dat'));

变长的示例
info.dat
21Alvin,Tolliver,1976,
19Kenneth,Baer,1963,
16Mary,Dube,1973,
......
ACCESS PARAMETERS (
RECORDS VARIABLE 2 FIELDS TERMINATED BY ','
(first_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4)
)
)
LOCATION ('info.dat')
);

分隔符示例
info.dat
Alvin,Tolliver,1976|Kenneth,Baer,1963|Mary,Dube,1973
......
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS (RECORDS DELIMITED BY '|' FIELDS TERMINATED BY ','
(first_name CHAR(7),
last_name CHAR(8),
year_of_birth CHAR(4)))
LOCATION ('info.dat'));

record_format_info部分，必先指定记录识别方式，其后指定其它参数：
如语言字符集badfilelogfilediscardfilereadsizedate_cacheskip

第三部分 字段定义部分 field_def Clause
如果不指定的，默认可以处理类以下格式
Alvin,Tolliver,1976
Kenneth,Baer,1963
不指定时，默认行为如下：
字段以逗号分隔，字段是字符类型，最大长度255，字段顺序与表中定义一致，不截除空格
此部分语法如下：
fields
delim_spec (可选)
trim_spec (可选)
MISSING FIELD VALUES ARE NULL (可选)
REJECT ROWS WITH ALL NULL FIELDS (可选)
field_list

delim_spec常用的是terminated by ','
trim_spec默认是ldrtrim 其它选项有notrim ltrim rtrim lrtrim

第四部分 column_transforms 列转换
column transforms (
column_name FROM LOBFILE (filename,filename,...)
)

info.dat
OnHandID,prodID,QTY,City,updDate
5001,1001,7,Atlanta,01-02-2004
5002,1001,7,Chicago,01-02-2004
5003,1001,7,Orlando,01-02-2004
5004,1002,7,Atlanta,01-02-2004
5005,1002,7,Chicago,01-02-2004
5006,1002,7,Orlando,01-02-2004

drop table t3;
create table t3 (
col1 number,col2 number,col3 number,col4 varchar2(30),col5 date)
organization external (
type oracle_loader
default directory data_dir
access parameters (
records delimited by newline
badfile data_dir:'t3.bad'
logfile data_dir:'t3.log'
discardfile data_dir:'t3.dis'
skip 1
readsize 1024
fields terminated by ','
missing field values are null
reject rows with all null fields
(col1,col2,col3,col4,col5 char(12) date_format date mask 'dd-mm-yyyy')
)
location ('prod_on_hand.txt')
)
/
select count(1) from t3;

注意： date_format 之前一定要有char(12)

-----------------------------------------------------------------------
SQL> create directory ws5_logdir as '/home/oracle/ws5';

Directory created.

SQL> grant read,write on directory ws5_srcdir to hr;

Grant succeeded.

SQL> c/src/log
1* grant read,write on directory ws5_logdir to hr
SQL> /

Grant succeeded.

SQL>
SQL> create table hr.prod_on_hand (
2 onhandid number,
3 prodid number,
4 qty number,
5 city varchar2(30),
6 upddate date)
7 organization external (
8 type oracle_loader
9 default directory ws5_srcdir
10 access parameters (
11 records delimited by newline
12 badfile ws5_logdir:'prod_on_hand.bad'
13 logfile ws5_logdir:'prod_on_hand.log'
14 discardfile ws5_logdir:'prod_on_hand.dis'
15 skip 1
16 readsize 1024
17 fields terminated by ','
18 (onhandid,prodid,qty,city,upddate char(12) date_format date mask 'dd-MON-yyyy')
19 ) location ('prod_on_hand.dat')
20 ) reject limit unlimited ;

Table created.

SQL> select count(1) from hr.prod_on_hand;

COUNT(1)
----------
83

SQL>

遗留问题：不能使用"DD-MON-YYYY"

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

转载于:http://blog.itpub.net/271063/viewspace-1054863/