oracle 外部表

CREATE TABLE "EXT_ENTRY_WORKFLOW" 
(
  "ENTRY_ID" VARCHAR2(18),
  "STEP_ID" VARCHAR2(8),
  "CREATE_DATE" DATE,
  "PROC_POS" VARCHAR2(4),
  "STATUS_CODE" VARCHAR2(32),
  "PROC_ER" VARCHAR2(8),
  "PROC_RESULT" VARCHAR2(8),
  "PREV_POS" VARCHAR2(4),
  "PREV_ER" VARCHAR2(8),
  "RECHK_MARK" VARCHAR2(1),
  "RECHK_COMM" VARCHAR2(255)
)
ORGANIZATION external 
(
  TYPE oracle_loader
  DEFAULT DIRECTORY ORA_DIR
  ACCESS PARAMETERS 
  (
    RECORDS DELIMITED BY 0x'214023' CHARACTERSET ZHS16GBK
    skip 1
    date_cache=0
    BADFILE 'ENTRY_WORKFLOW.bad'
    LOGFILE 'ENTRY_WORKFLOW.log'
    READSIZE 99999999
    FIELDS TERMINATED BY "~#!" LDRTRIM 
    MISSING FIELD VALUES ARE NULL 
    REJECT ROWS WITH ALL NULL FIELDS 
    (
      "ENTRY_ID" CHAR(255)
        TERMINATED BY "~#!",
      "STEP_ID" CHAR(255)
        TERMINATED BY "~#!",
      "CREATE_DATE" CHAR(255)
        TERMINATED BY "~#!"
        DATE_FORMAT DATE MASK "yyyy-mm-dd hh24:mi:ss",
      "PROC_POS" CHAR(255)
        TERMINATED BY "~#!",
      "STATUS_CODE" CHAR(255)
        TERMINATED BY "~#!",
      "PROC_ER" CHAR(255)
        TERMINATED BY "~#!",
      "PROC_RESULT" CHAR(255)
        TERMINATED BY "~#!",
      "PREV_POS" CHAR(255)
        TERMINATED BY "~#!",
      "PREV_ER" CHAR(255)
        TERMINATED BY "~#!",
      "RECHK_MARK" CHAR(255)
        TERMINATED BY "~#!",
      "RECHK_COMM" CHAR(255)
        TERMINATED BY "~#!"
    )
  )
  location 
  (
    'ENTRY_WORKFLOW.TXT'
  )
)REJECT LIMIT UNLIMITED

外部表创建

上面都一样，下面是这样的

如果在plsql里执行。要用命令窗口，不要用sql窗口执行（可能会有一些编码问题。）

ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY ORA_DIR
ACCESS PARAMETERS
(
    RECORDS DELIMITED BY 0x'214023' CHARACTERSET ZHS16GBK
    skip 1
    date_cache=0
    BADFILE 'ENTRY_WORKFLOW.bad'
    LOGFILE 'ENTRY_WORKFLOW.log'
    READSIZE 99999999
    FIELDS TERMINATED BY "~#!" LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (……

external 代表外部表

ORA_DIR 表空间，select * from dba_data_files可以找到对应的目录。

…… 里面的内容可以写一个ctl

load data
infile  'D:databag01EXTERNAL_ENTRY_WORKFLOW.TXT' "str X'214023'"
TRUNCATE into table ENTRY_WORKFLOW
fields terminated by '~#!'
trailing nullcols
(
ENTRY_ID,
STEP_ID,
CREATE_DATE DATE "yyyy-mm-dd hh24:mi:ss",
PROC_POS,
STATUS_CODE,
PROC_ER,
PROC_RESULT,
PREV_POS,
PREV_ER,
RECHK_MARK,
RECHK_COMM
)

创ctl

然后用命令去执行ctl，指定一个log，然后从日志里直接复制出来放到……里就行了。