-- 导入:(在sql命令行执行)
OPTIONS (errors=400000,rows=50000,bindsize=10485760,direct=true)
LOAD DATA infile '/data/esbftp/tang/ceshi/COUPONS_SELLBACK.txt'
append INTO TABLE COUPONS_SELLBACK FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(MONTH_ID,DAY_ID,MERCHANT_NAME,MERCHANT_CODE,ACTIVITY_CODE,PROVINCE_SN,SEND_SELLBACK_DATE,STATUS,AMOUNT,COUPONS_ACCOUNT,PROVINCIAL_BUSINESS_CODE,WAITING_SN,WAITING_AMOUNT,COUPONS_NUMBER,COUPONS_TYPE,USER_PROVINCE char(1000),USER_CITY,INITIAL_SN);
注意:每个字段默认长度为255;如果长度大于255 在字段后面加 char(1000)
-- 导出:(在sql命令行执行)
set echo on ;
set colsep ',';
set feedback off;
set heading off;
set LINESIZE 1000;
set trimout on ;
set trimspool on ;
set pagesize 0 ;
spool /data/resultToESB/SPACE/20180928/ceshi.csv;
SELECT TASK_ID||','||USER_TEL||','||CREATE_DATE||','||SEND_dATE FROM INTF_RES_SPC_STATION;
spool off
--入库注意
总结:导入数据使用SQL*LOADER中optionally enclosed时要么数据左边不能带引号;如果有引号左右都必须为奇数个,但不必相等。