Oracle 9i中引入的外部表答應您將一個順序文本文件映射到一個Oracle表定義中。一旦您定義了外部表，您就能應用SQL SELECT語句的所有功能——包括并行查詢——來處理它。 外部表以SQL*Loader為基礎，但定義它們的語法有所不同。假如您已經熟悉SQL*Loader，就有一條捷徑:應用SQL*Loader本身生成一段為您建立外部表定義的腳本?！ ‘斈砑右粋€SQL*Loader命令行時，參數EXTERNAL_TABLE=GENERATE_ONLY，它將把系統使用的控制文件轉譯成一段SQL腳本，并輸出到系統的日志文件中。您可以對日志文件進行編輯，或復制并粘貼這些代碼到新的腳本中?！ ×斜鞟中是一個用來將不同長度的文件記錄加載到數據庫中的控制文件。執行下面的命令不會加載行，但會在日志文件中輸出一個外部表。列表B中為輸出內容。 列表A：
LOAD DATA
INFILE 'loademp.dat'
BADFILE 'loademp.bad'
DISCARDFILE 'loademp.dsc'
APPEND
INTO TABLE emp
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr, hiredate, sal, comm, deptno) 列表B：SQL*Loader: Release 10.2.0.1.0 - PRodUCtion on Tue Sep 19 11:07:46 2006Copyright (c) 1982, 2005, Oracle. All rights reserved.Control File: loademp.ctl
Data File: loademp.dat Bad File: loademp.bad Discard File: loademp.dsc (Allow all discards)Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External TableTable EMP, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPNO FIRST * , O(") CHARACTER
ENAME NEXT * , O(") CHARACTER
JOB NEXT * , O(") CHARACTER
MGR NEXT * , O(") CHARACTER
HIREDATE NEXT * , O(") CHARACTER
SAL NEXT * , O(") CHARACTER
COMM NEXT * , O(") CHARACTER
DEPTNO NEXT * , O(") CHARACTER
CREATE DirectorY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'Z:/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
( "EMPNO" NUMBER(4), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2)
)
ORGANIZATION external
( TYPE oracle_loader DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 access PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252 BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'loademp.bad' DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'loademp.dsc' LOGFILE 'loademp.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( "EMPNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "ENAME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "JOB" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "MGR" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "HIREDATE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "SAL" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "COMM" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"', "DEPTNO" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' ) ) location ( 'loademp.dat' )
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
)
SELECT "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO"
FROM "SYS_SQLLDR_X_EXT_EMP"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_EMP"
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000Run began on Tue Sep 19 11:07:46 2006
Run ended on Tue Sep 19 11:07:49 2006 Elapsed time was: 00:00:02.81
CPU time was: 00:00:00.09 sqlldr scott/tiger CONTROL=loademp.ctl EXTERNAL_TABLE=GENERATE_ONLY 首先，腳本建立一個指向包含輸入文件目錄的目錄對象。目錄對象給特定操作系統的完整路徑名取一些簡短、獨立于平臺的名稱。用CREATE DIRECTORY命令建立關聯。要這樣做，我們需要具有CREATE ANY DIRECTORY系統權限，或讓數據庫治理員替您運行它以獲得許可。而且，系統生成的名稱不利記憶。在運行前，最好對腳本進行編輯，取一個更有意義的名稱?！ 〗酉聛恚_本中包含CREATE TABLE語句本身。這個語句參考目錄對象(假如您修改了上面的名稱，對它進行編輯)，文件被加載，執行ORGANIZATION EXTERNAL子句。它還包含描述文本文件布局的語法。 最后，腳本中包含一個在子查詢的外部表中選擇的INSERT(插入)語句范例，以及在處理完畢后終止外部表和目錄的語句?！ ob Watkins(OCP、MCDBA、MCSE、MCT)是一位有25年經驗的計算機專業人士，從事過技術培訓師、顧問與數據庫治理員等職。
