Oracle External Tables

Grammar:

CREATE TABLE Table name (

Column name 1, column Name 2, ...

)

Organization External # # #说明创建外部表

(

Type access Types # # #一般是 Oracle_loader, Oracle_datapump

Default directory path name # # # #指定默认目录对象

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

Records delimited by set separator # # #一般是newline

[Badfile path name: ' filename '] # #存放错误日志的目录和文件名

[LogFile path name: ' filename '] # #日志的目录和文件名

[Discardfile path name: ' filename '] # # #废弃文件存放的目录和文件名

[Missing field values is null] # #无值字段的处理
[Reject rows with any null fields] # #无值行的处理

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

(column name 1, column Name 2, ...)

)

Location (path name: ' Data file name ')

)

[Reject limit (or unlimited) value] # # #设置多少行转换失败时返回oracle错误, default is 0

[Parallel]

Example:

1. Create three directories under the system

/home/oracle/test_folder/data--Data file directory

Touch Test.dat--Data file

Data entry

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

/home/oracle/test_folder/log--Log file directory

Touch Test.log--log file

/home/oracle/test_folder/bad--Error file directory

Touch Test.bad--Error file

2. Create a Directory

Landing Conn/as SYSDBA

Create or replace directory Data_dir as '/home/oracle/test_folder/data '; --Data file directory
Create or replace directory Log_dir as '/home/oracle/test_folder/log ';--Log file directory
Create or replace directory Bad_dir as '/home/oracle/test_folder/bad ';--Error file directory

3. Authorization

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. Create a table

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 data_dir: ' Test.bad '
LogFile data_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;

SELECT * FROM Ext_emp

Oracle External Tables