【赛迪网－it技术报道】外表(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.

瞧，成功了。外表就这么简单。可是只有二步啊，第三步在哪里呢？你也许会问。还有啊，userlist.txt要固定的格式吗？create table...的语法这样的狂复杂，每一项都是什么含义呢？

这就是第三步要教给大家的东西了。

第三步：理解外表数据结构与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

...

瞧，原来我们更本不用担心怎么写create external table的语句呢。sqlldr就可以帮我们生成了！　

您是不是已经学会了？