外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。在这篇文章里，我们为大家演示了三步就掌握oracle外表过程。通过这次学习，也许大家就会发展原来学习oracle也是好容易哦。
第一步：创建目录并授权
目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。
sys@TEST!ls /home/oracle/temp
user.ctl
userlist.txt
user.log
rudolf@TEST
sys@TESTconn system/alibaba
Connected.
sys@TEST
sys@TESTCREATE DIRECTORY
TEMP AS '/home/oracle/temp/';
Directory created.
sys@TESTgrant read,write on directory TEMP to rudolf;
Grant succeeded.
第二步：创建外表与测试
rudolf@TESTCREATE TABLE "USERLIST"
2
(
3
ID NUMBER,
4
USERNAME VARCHAR2(30),
5
EMAIL VARCHAR2(128)
6
)
7
ORGANIZATION external
8
(
9
TYPE oracle_loader
10
DEFAULT DIRECTORY TEMP
11
ACCESS PARAMETERS
12
(
13
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
14
BADFILE 'TEMP':'userlist.bad'
15
DISCARDFILE 'TEMP':'userlist.dis'
16
LOGFILE 'TEMP':'user.log'
17
READSIZE 1048576
18
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
19
MISSING FIELD VALUES ARE NULL
20
REJECT ROWS WITH ALL NULL FIELDS
21
(
22
ID CHAR(30)
23
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
24
USERNAME CHAR(30)
25
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
26
EMAIL CHAR(128)
27
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
28
)
29
)
30
location
31
(
32
'userlist.txt'
33
)
34
)REJECT LIMIT UNLIMITED
35
rudolf@TEST/
Table created.
rudolf@TESTl
1
select id,username from userlist where rownum < 10
2*
rudolf@TEST/
ID USERNAME
---------- ------------------------------
1 RudolfLu
3 tomgu
6 coug
7 chao_ping
8 parrotao
9 cnoug
10 FilsDeDragon
11 Dragon
9 rows selected.
第三步：理解外表数据结构与create table ... organization external语法
大家都用过sqlldr吧？外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了。那么语法呢？嘿嘿，别急，让我们先来做个sqlldr的练习吧：
[oracle@rac1 temp]$ head -10 userlist.txt
1,"RudolfLu"
3,"tomgu"
6,"coug"
7,"chao_ping"
8,"parrotao"
9,"cnoug"
10,"FilsDeDragon"
11,"Dragon"
15,"Xavier"
[oracle@rac1 temp]$ cat user.ctl
LOAD
INFILE '/home/oracle/temp/userlist.txt'
badfile '/home/oracle/temp/userlist.bad'
discardfile '/home/oracle/temp/userlist.dis'
APPEND
INTO TABLE userlist
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
( id
char(30),
username char(30)
)
rudolf@TESTcreate table userlist
2
(id
number,
3
username varchar2(30)
4
);
Table created.
rudolf@TEST!
[oracle@rac1 temp]$ sqlldr rudolf/nix@test2.world control=./user.ctl external_table=GENERATE_ONLY
注意，我们加了一个external_table的参数。它的作用是告诉sqlldr不用真实load数据，而是生成包含external table 创建脚本的log文件。
[oracle@rac1 temp]$ ls
user.ctl
userlist.txt
user.log
[oracle@rac1 temp]$ cat user.log
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
Control File:
./user.ctl
Data File:
/home/oracle/temp/userlist.txt
Bad File:
/home/oracle/temp/userlist.bad
Discard File: /home/oracle/temp/userlist.dis
...
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/temp/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_USERLIST"
(
ID NUMBER,
USERNAME VARCHAR2(30)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.dis'
LOGFILE 'user.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
USERNAME CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'userlist.txt'
)
)REJECT LIMIT UNLIMITED
...