使用sqlldr的控制文件生成创建外部表的语句

先创建一个目录

SQL> create or replace directory dir1 as '/home/oracle';Directory created

根据sqlldr的控制文件来生成创建外部表的语句,先查看SQLLDR向DEPT表中加载批量数据的例子,如下：

[oracle@jy ~]$ cat demo21.ctlLOAD DATAINFILE *INTO TABLE DEPTFIELDS TERMINATED BY ','(DEPTNO, DNAME, LOC )BEGINDATA10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance,Virginia

以下SQLLDR命令会为我们的外部表生成CREATE TABLE语句：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo21.ctl external_table=generate_onlySQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.

EXTERNAL_TABLE参数有以下3个值：

NOT_USED:这是默认值.EXECUTE:这个值说明SQLLDR不会生成并执行一个SQL INSERT语句;而是会创建一个外部表,并使用一个批量SQL语句来加载.GENERATE_ONLY:这个值使得SQLLDR并不具体加载任何数据,而只是会生成所执行的SQL DDL和DML语句,并放到它创建的日志文 件中.但是要注意:DIRECT=TRUE覆盖EXTENAL_TABLE=GENERATE_ONLY.如果指定了DIRECT=TRUE,则会加载数据,而不会生成外部表.

使用GENERATE_ONLY时,可以在demo21.log文件中看到以下内容：

[oracle@jy ~]$ cat demo21.logSQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013Copyright (c) 1982, 2005, Oracle.  All rights reserved.Control File:   demo21.ctlData File:      demo21.ctl  Bad File:     demo21.bad  Discard File:  none specified (Allow all discards)Number to load: ALLNumber to skip: 0Errors allowed: 50Continuation:    none specifiedPath used:      External TableTable DEPT, loaded from every logical record.Insert option in effect for this table: INSERT   Column Name                  Position   Len  Term Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------DEPTNO                              FIRST     *   ,       CHARACTERDNAME                                NEXT     *   ,       CHARACTERLOC                                  NEXT     *   ,       CHARACTER

下面为外部表生成CREATE TABLE语句：

CREATE TABLE statement for external table:------------------------------------------------------------------------CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"(  "DEPTNO" NUMBER(2),  "DNAME" VARCHAR2(14),  "LOC" VARCHAR2(14))

SQLLDR已经登录到数据库;只有这样它才知道这个外部表定义中要用的具体数据类型(例如,DEPTNO是一个NUMBER(2)). SQLLDR根据数据字典来确定这些数据类型.

ORGANIZATION EXTERNAL子句告诉Oracle:这不是一个"正常"表.

ORGANIZATION external(  TYPE oracle_loader  DEFAULT DIRECTORY DIR1  ACCESS PARAMETERS  (    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK    BADFILE 'DIR1':'demo21.bad'    LOGFILE 'demo21.log_xt'    READSIZE 1048576    SKIP 6    FIELDS TERMINATED BY "," LDRTRIM    REJECT ROWS WITH ALL NULL FIELDS    (      "DEPTNO" CHAR(255)        TERMINATED BY ",",      "DNAME" CHAR(255)        TERMINATED BY ",",      "LOC" CHAR(255)        TERMINATED BY ","    )  )  location  (    'demo21.ctl'  ))REJECT LIMIT UNLIMITED

ORACEL_LOADER类型是目前支持的两种类型之一(Oracle9i中只支持这一种类型),另一种类型是ORACLE_DATAPUMP,

这是Oracle 10g及以上版本中Oracle的专用数据泵格式.这种格式不仅可以用于加载数据,也可以卸载数据.

这些访问参数显示了如何建立一个外部表,使之能像SQLLDR一样几乎以同样的方式处理文件:

RECORDS:记录默认以换行符结束,SQLLDR中的记录就是如此.

BADFILE:在刚创建的目录中建立了一个坏文件(无法处理的记录都记录到这个文件中).

LOGFILE:在当前的工作目录中记录了一个等价于SQLLDR日志文件的日志文件.

READSIZE:这是Oracle读取输入数据文件所用的默认缓冲区.在这里是1MB.如果采用专用服务器模式,这个内存来自PGA,如果 采用共享服务器模式,则来自SGA,它用于缓存输入数据文件中对应一个会话的信息.

SKIP 6:在确定了应该跳过输入文件中的多少记录.你可能会问:为什么有skip 6.是这样,在这个例子中使用了INFILE *;使用SKIP 6就是跳过控制文件本身来得到内嵌的数据.如果没有使用INFILE *,就根本不会有SKIP子句.

FIELDS TERMINATED BY:这与控制文件中的用法一样.不过,外部表增加LDRTRIM,这代表Loader TRIM.这是一种截断模式,模 拟了SQLLDR截断数据的默认做法.还有另外一些选项,包括LRTRIM,LTRIM和RTRIM,表示左截断/右截断空白符;NOTRIM表示保 留所有前导/尾随的空白符.

REJECT ROWS WITH ALL NULL FIELDS:这导致外部表会在坏文件中记录所有全空的行,而且不加载这些行.

列定义本身:这是有关所期望输入数据值的元数据.它们是所加载数据文件中的字符串,长度最多可达255个字符(SQLLDR的默 认大小),以逗号(,)结束,还可以选择用引号括起来.

location部分告诉Oracle所加载文件的文件名,在这里就是demo21.ctl,因为我们在原控制文件中使用了INFILE *.控制文件中的下一条语句是默认的INSERT,可以用于从外部表本身加载表:

INSERT statements used to load internal tables:------------------------------------------------------------------------INSERT /*+ append */ INTO DEPT(  DEPTNO,  DNAME,  LOC)SELECT  "DEPTNO",  "DNAME",  "LOC"FROM "SYS_SQLLDR_X_EXT_DEPT"

如果可能的话,这会执行一个逻辑上与直接路径加载等价的操作(如果可以遵循APPEND提示;如果存在触发器或外键约束,可能 不允许发生直接路径操作).

最后,在日志文件中,我们会看到一些语句,这些语句可以用于删除加载完成之后SQLLDR我我们创建的对象:

statements to cleanup objects created by previous statements:------------------------------------------------------------------------DROP TABLE "SYS_SQLLDR_X_EXT_DEPT"

可能会看到日志文件中有一个CREATE DIRECTORY语句(也可能看不到).在生成外部表脚本期间,SQLLDR连接到数据库,并查询 数据字典来查看是否已经存在合适的目录.在这个例子中,由于有合适的目录,所以SQLLDR为我们选择了我们之前所创建的目录DIR1

CREATE DIRECTORY statements needed for files------------------------------------------------------------------------CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle'

通过sqldr生成外部全创建语句的完整语句如下:

CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"(  "DEPTNO" NUMBER(2),  "DNAME" VARCHAR2(14),  "LOC" VARCHAR2(14))ORGANIZATION external(  TYPE oracle_loader  DEFAULT DIRECTORY DIR1  ACCESS PARAMETERS  (    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK    BADFILE 'DIR1':'demo21.bad'    LOGFILE 'demo21.log_xt'    READSIZE 1048576    SKIP 6    FIELDS TERMINATED BY "," LDRTRIM    REJECT ROWS WITH ALL NULL FIELDS    (      "DEPTNO" CHAR(255)        TERMINATED BY ",",      "DNAME" CHAR(255)        TERMINATED BY ",",      "LOC" CHAR(255)        TERMINATED BY ","    )  )  location  (    'demo21.ctl'  ))REJECT LIMIT UNLIMITED;

执行创建外部表的语句:

jy@JINGYONG> CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT"  2  (  3    "DEPTNO" NUMBER(2),  4    "DNAME" VARCHAR2(14),  5    "LOC" VARCHAR2(14)  6  )  7  ORGANIZATION external  8  (  9    TYPE oracle_loader 10    DEFAULT DIRECTORY DIR1 11    ACCESS PARAMETERS 12    ( 13      RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK 14      BADFILE 'DIR1':'demo21.bad' 15      LOGFILE 'demo21.log_xt' 16      READSIZE 1048576 17      SKIP 6 18      FIELDS TERMINATED BY "," LDRTRIM 19      REJECT ROWS WITH ALL NULL FIELDS 20      ( 21        "DEPTNO" CHAR(255) 22          TERMINATED BY ",", 23        "DNAME" CHAR(255) 24          TERMINATED BY ",", 25        "LOC" CHAR(255) 26          TERMINATED BY "," 27      ) 28    ) 29    location 30    ( 31      'demo21.ctl' 32    ) 33  )REJECT LIMIT UNLIMITED;表已创建。

然后通过所创建的外部表向dept表插入数据:

jy@JINGYONG> INSERT /*+ append */ INTO DEPT  2  (  3    DEPTNO,  4    DNAME,  5    LOC  6  )  7  SELECT  8    "DEPTNO",  9    "DNAME", 10    "LOC" 11  FROM "SYS_SQLLDR_X_EXT_DEPT";已创建4行。jy@JINGYONG> commit;提交完成。

创建了日志文件

[oracle@jy ~]$ ls -lrt demo21.log_xt-rw-r--r-- 1 oracle oinstall 737 Oct  5 00:43 demo21.log_xtSQL> select * from jy.dept;DEPTNO DNAME          LOC            ENTIRE_LINE  LAST_UPDATED COMMENTS------ -------------- -------------- ------------ ------------ ---------    10 Sales          Virginia    20 Accounting     Virginia    30 Consulting     Virginia    40 Finance        Virginia

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-773790/，如需转载，请注明出处，否则将追究法律责任。