sql*loader数据文件和控制文件生成外部表创建语句，包含date类型字段

数据文件（tab键分隔）：
56      november,15,1980        baker   mary    alice   09/01/2004
87      december,10,1970        roper   lisa    marie1
89      december,21,1988        roper   lisa1   marie2
76      december,22,1972        roper   lisa2   marie3
57      december,29,1990        roper   lisa3   marie4
39      december,27,1967        roper   lisa4   marie5
45      december,01,1989        roper   lisa5   marie6
88      december,17,1960        roper   lisa6   marie7  01/01/1999

控制文件：

load data
infile 'info.dat'
badfile 'bad.dat'
discardfile 'dis.dat'
append
into table t_info
fields terminated by '  '          --此处用什么字符可以替代tab键？
TRAILING NULLCOLS
(
x1,
x2,
x3,
x4,
x5,
x6)

生成语句：
sqlldr userid=hr/hr control=info.ctl external_table=GENERATE_ONLY

查看日志原文：
CREATE TABLE "SYS_SQLLDR_X_EXT_T_INFO"
(
  "X1" VARCHAR2(20),
  "X2" VARCHAR2(20),
  "X3" VARCHAR2(20),
  "X4" VARCHAR2(20),
  "X5" VARCHAR2(20),
  "X6" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DEF_DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'DEF_DIR1':'bad.dat'
    DISCARDFILE 'DEF_DIR1':'dis.dat'
    LOGFILE 'info.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY "      " LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "X1" CHAR(255)
        TERMINATED BY " ",
      "X2" CHAR(255)
        TERMINATED BY " ",
      "X3" CHAR(255)
        TERMINATED BY " ",
      "X4" CHAR(255)
        TERMINATED BY " ",
      "X5" CHAR(255)
      "X6" CHAR(255)
        TERMINATED BY " "
    )
  )
  location
  (
    'info.dat'
  )
)REJECT LIMIT UNLIMITED

注意有几处需要修改，修改后如下：
CREATE TABLE ABC
(
  "X1" VARCHAR2(20),
  "X2" VARCHAR2(20),
  "X3" VARCHAR2(20),
  "X4" VARCHAR2(20),
  "X5" VARCHAR2(20),
  "X6" DATE
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY DEF_DIR1
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'DEF_DIR1':'bad.dat'
    DISCARDFILE 'DEF_DIR1':'dis.dat'
    LOGFILE 'info.log_xt'
    READSIZE 1048576
    FIELDS TERMINATED BY x'09' LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "X1" CHAR(255),
      "X2" CHAR(255),
      "X3" CHAR(255),
      "X4" CHAR(255),
      "X5" CHAR(255),
      "X6" CHAR(255)  date_format DATE mask "mm/dd/yyyy"
    )
  )
  location
  (
    'info.dat'
  )
)REJECT LIMIT UNLIMITED;

其中红色字体部分做过修改。

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

转载于:http://blog.itpub.net/12355989/viewspace-704667/