外表（external table）就像普通的表對像一樣可以select等只是它是只讀的數據庫中只保存了表結構的描述表數據卻沒有存放在數據庫內而是存放在了文件系統上當用戶想偶爾使用數據庫外的結構化數據時用起外表來就非常方便甚至比sqlldr都要方便的多在這篇文章裡我們為大家演示了
三步就掌握Oracle外表過程通過這次學習也許大家就會發展原來學習oracle也是好容易哦
第一步創建目錄並授權
目錄是數據文件的存放目標數據文件通常要求是文本文件這個過程在i以前是需要配置utl_file_dir參數的 復制內容到剪貼板
代碼
!ls>sys@TEST>!ls /home/oracle/temp
userctl userlisttxt userlog
rudolf@TEST>
conn>sys@TEST>conn system/alibaba
Connected
sys@TEST>
CREATE>sys@TEST>CREATE DIRECTORY TEMP AS /home/oracle/temp/;
Directory created
grant>sys@TEST>grant readwrite on directory TEMP to rudolf;
Grant succeeded
第二步創建外表與測試 復制內容到剪貼板
代碼
cellPadding= width=% bgColor=#ffffff border=>
CREATE>rudolf@TEST>CREATE TABLE USERLIST
(
ID NUMBER
USERNAME VARCHAR()
EMAIL VARCHAR()
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TEMP
Access PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET USASCII
BADFILE TEMP:userlistbad
DISCARDFILE TEMP:userlistdis
LOGFILE TEMP:userlog
READSIZE
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
cellPadding= width=% bgColor=#ffffff border=>
USERNAME CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
EMAIL CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
)
)
location
(
userlisttxt
)
)REJECT LIMIT UNLIMITED
/>rudolf@TEST>/
Table created
l>rudolf@TEST>l
select idusername from userlist where rownum <
*
/>rudolf@TEST>/
ID USERNAME
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
rows selected
瞧成功了外表就這麼簡單可是只有二步啊第三步在哪裡呢？你也許會問還有啊userlisttxt要固定的格式嗎？create table……的語法這樣的狂復雜每一項都是什麼含義呢？
這就是第三步要教給大家的東西了
第三步理解外表數據結構與create table …… organization external語法
大家都用過sqlldr吧？外表的數據文件的結構呢就同sqlldr能讀的數據文件結構一樣了那麼語法呢？嘿嘿別急讓我們先來做個sqlldr的練習吧
cellPadding= width=% bgColor=#ffffff border=>
[oracle@rac temp]$ head userlisttxt
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
Xavier
[oracle@rac temp]$ cat userctl
LOAD
INFILE /home/oracle/temp/userlisttxt
badfile /home/oracle/temp/userlistbad
discardfile /home/oracle/temp/userlistdis
cellPadding= width=% bgColor=#ffffff border=>
APPEND
INTO TABLE userlist
fields terminated by optionally enclosed by
trailing nullcols
( id char()
username char()
)
create>rudolf@TEST>create table userlist
(id number
username varchar()
);
Table created
rudolf@TEST>!
[oracle@rac temp]$ sqlldr rudolf/nix@testworld control=/userctl external_table=GENERATE_ONLY
注意
我們加了一個external_table的參數
它的作用是告訴sqlldr不用真實load數據
而是生成包含external table 創建腳本的log文件
cellPadding=
width=
%
bgColor=#ffffff border=
>
[oracle@rac
temp]$ ls
user
ctl userlist
txt user
log
[oracle@rac
temp]$ cat user
log
SQL*Loader: Release
Production on Wed Dec
:
:
Copyright (c)
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_
AS
/home/oracle/temp/
CREATE TABLE statement for external table:
CREATE TABLE
SYS_SQLLDR_X_EXT_USERLIST
(
ID NUMBER
USERNAME VARCHAR
(
)
)
ORGANIZATION external
(
TYPE oracle_loader
cellPadding=
width=
%
bgColor=#ffffff border=
>
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US
ASCII
BADFILE
SYS_SQLLDR_XT_TMPDIR_
:
userlist
bad
DISCARDFILE
SYS_SQLLDR_XT_TMPDIR_
:
userlist
dis
LOGFILE
user
log_xt
READSIZE
FIELDS TERMINATED BY
OPTIONALLY ENCLOSED BY
LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR(
)
TERMINATED BY
OPTIONALLY ENCLOSED BY
USERNAME CHAR(
)
TERMINATED BY
OPTIONALLY ENCLOSED BY
)
)
location
(
userlist
txt
)
)REJECT LIMIT UNLIMITED
瞧原來我們更本不用擔心怎麼寫create external table的語句呢sqlldr就可以幫我們生成了！
您是不是已經學會了？
From:http://tw.wingwit.com/Article/program/Oracle/201311/18052.html
