使用sqlldr的控制檔案生成建立外部表的語句
先建立一個目錄

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

Directory created

根據sqlldr的控制檔案來生成建立外部表的語句,先檢視SQLLDR向DEPT表中載入批次資料的例子,如下：

[oracle@jy ~]$ cat demo21.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

以下SQLLDR命令會為我們的外部表生成CREATE TABLE語句：

[oracle@jy ~]$ sqlldr userid=jy/jy control=demo21.ctl external_table=generate_only

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

Copyright (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.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sat Oct 5 00:22:15 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   demo21.ctl
Data File:      demo21.ctl
  Bad File:     demo21.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation:    none specified
Path used:      External Table

Table DEPT, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO                              FIRST     *   ,       CHARACTER
DNAME                                NEXT     *   ,       CHARACTER
LOC                                  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_xt

SQL> select * from jy.dept;

DEPTNO DNAME          LOC            ENTIRE_LINE  LAST_UPDATED COMMENTS
------ -------------- -------------- ------------ ------------ ---------
    10 Sales          Virginia
    20 Accounting     Virginia
    30 Consulting     Virginia
    40 Finance        Virginia

使用sqlloader控制檔案生成外部表建立語句的一個例子

相關文章