外部表筆記一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"