从Oracle 9i SQL*Loader中生成外部表

来源：作者：出处：巧巧读书 2006-10-24 进入讨论组

　　Oracle 9i中引入的外部表允许您将一个顺序文本文件映射到一个Oracle表定义中。一旦您定义了外部表，您就能应用SQL SELECT语句的所有功能——包括并行查询——来处理它。

　　外部表以SQL*Loader为基础，但定义它们的语法有所不同。如果您已经熟悉SQL*Loader，就有一条捷径:应用SQL*Loader本身生成一段为您建立外部表定义的脚本。

　　当您添加一个SQL*Loader命令行时，参数EXTERNAL_TABLE=GENERATE_ONLY，它将把系统使用的控制文件转译成一段SQL脚本，并输出到系统的日志文件中。您可以对日志文件进行编辑，或复制并粘贴这些代码到新的脚本中。

　　列表A中是一个用来将不同长度的文件记录加载到数据库中的控制文件。执行下面的命令不会加载行，但会在日志文件中输出一个外部表。列表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 2006

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 Table

Table 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_00000

Run 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(插入)语句范例，以及在处理完毕后终止外部表和目录的语句。

　　Bob Watkins(OCP、MCDBA、MCSE、MCT)是一位有25年经验的计算机专业人士，从事过技术培训师、顾问与数据库管理员等职。

收藏 http://www.qqread.com/oracle/2006/10/y246848.html