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

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

Copyright (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 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年经验的计算机专业人士,从事过技术培训师、顾问与数据库治理员等职。

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航