外表(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@TEST>conn system/alibaba

　　Connected.

　　sys@TEST>

　　sys@TEST>CREATE DIRECTORY

　　TEMP AS '/home/oracle/temp/';

　　Directory created.

　　sys@TEST>grant read,write on directory TEMP to rudolf;

　　Grant succeeded.

　　第二步：创建外表与测试

　　rudolf@TEST>CREATE 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@TEST>l

　　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@TEST>create 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

　　...