oracle 外部表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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,指定一个log,然后从日志里直接复制出来放到……里就行了。