CREATE TABLE "EXT_ENTRY_WORKFLOW"
(
"ENTRY_ID" VARCHAR2(),
"STEP_ID" VARCHAR2(),
"CREATE_DATE" DATE,
"PROC_POS" VARCHAR2(),
"STATUS_CODE" VARCHAR2(),
"PROC_ER" VARCHAR2(),
"PROC_RESULT" VARCHAR2(),
"PREV_POS" VARCHAR2(),
"PREV_ER" VARCHAR2(),
"RECHK_MARK" VARCHAR2(),
"RECHK_COMM" VARCHAR2()
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY ORA_DIR
ACCESS PARAMETERS
(
RECORDS DELIMITED BY 0x'' CHARACTERSET ZHS16GBK
skip
date_cache=
BADFILE 'ENTRY_WORKFLOW.bad'
LOGFILE 'ENTRY_WORKFLOW.log'
READSIZE
FIELDS TERMINATED BY "~#!" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"ENTRY_ID" CHAR()
TERMINATED BY "~#!",
"STEP_ID" CHAR()
TERMINATED BY "~#!",
"CREATE_DATE" CHAR()
TERMINATED BY "~#!"
DATE_FORMAT DATE MASK "yyyy-mm-dd hh24:mi:ss",
"PROC_POS" CHAR()
TERMINATED BY "~#!",
"STATUS_CODE" CHAR()
TERMINATED BY "~#!",
"PROC_ER" CHAR()
TERMINATED BY "~#!",
"PROC_RESULT" CHAR()
TERMINATED BY "~#!",
"PREV_POS" CHAR()
TERMINATED BY "~#!",
"PREV_ER" CHAR()
TERMINATED BY "~#!",
"RECHK_MARK" CHAR()
TERMINATED BY "~#!",
"RECHK_COMM" CHAR()
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:\databag01\EXTERNAL_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，然后从日志里直接复制出来放到……里就行了。