xls 分隔符 oracle,oracle加载分隔符文件

需求：从核心数据库中卸载一批数据，然后加载到oracle中，做报表分析。

步骤：

1、卸数

从核心系统(数据库：informix，操作系统aix)定时卸数到unl文件中。需要加环境变量

a.unloaddata.sh

. /home/cbsdev/.profile

dbaccess cbsdev unloaddata.sql>>dblog.log

b.unloaddata.sql

unload to 'sn14.unl' select * from sn14;

unload to 'sn20.unl' select * from sn20;

unload to 'ss14.unl' select * from ss14;

c.crontab(vi crontab)，每天1点调shell

0 1 * * * /home/cbsdev/unloaddata.sh

2、ftp取数

a.ftpfile.bat

@echo off

echo -----------------------------------------------------------------

echo * *

echo * 开始获取10.20.0.61ftp上的数据 *

echo * *

echo -----------------------------------------------------------------

ftp -i -n -s:"profile.ini" >>ftp.log

exit

b.profile.ini

open 10.20.0.61

user cbsdev cbsdev

ls *.unl

mget *.unl

bye

quit

3、建外部表(S2_ext_sa4.ddl)

-- Create table

create table EXT_SA4

(

FA4HH INTEGER,

FA4YWLX INTEGER,

FA4BIZ INTEGER,

FA4KMH INTEGER,

FA4ZL INTEGER,

FA4KMJB INTEGER,

FA4YEF INTEGER,

FA4ZRJYE NUMBER,

FA4ZRDYE NUMBER,

FA4JRJYE NUMBER,

FA4JRDYE NUMBER,

FA4ZZJPZS INTEGER,

FA4ZZDPZS INTEGER,

FA4ZZJFSE NUMBER,

FA4ZZDFSE NUMBER,

FA4XJJPZS INTEGER,

FA4XJDPZS INTEGER,

FA4XJJFSE NUMBER,

FA4XJDFSE NUMBER,

FA4ZRHS INTEGER,

FA4JRHS INTEGER,

FA4KHS INTEGER,

FA4XHS INTEGER,

FA4LX INTEGER,

FA4QCRQ VARCHAR2(10),

FA4BY1 INTEGER,

FA4BY2 INTEGER,

FA4BY3 VARCHAR2(15),

FA4BY4 NUMBER

)

organization external

(

type ORACLE_LOADER

default directory EXT_DIR

access parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_TABCHM01.txt'

LOGFILE 'log_TABCHM01.txt'

FIELDS TERMINATED BY '|'

MISSING FIELD VALUES ARE NULL

(

fa4hh,

fa4ywlx,

fa4biz,

fa4kmh,

fa4zl,

fa4kmjb,

fa4yef,

fa4zrjye,

fa4zrdye,

fa4jrjye,

fa4jrdye,

fa4zzjpzs,

fa4zzdpzs,

fa4zzjfse,

fa4zzdfse,

fa4xjjpzs,

fa4xjdpzs,

fa4xjjfse,

fa4xjdfse,

fa4zrhs,

fa4jrhs,

fa4khs,

fa4xhs,

fa4lx,

fa4qcrq,

fa4by1,

fa4by2,

fa4by3,

fa4by4

)

)

location (EXT_DIR:'sa4.unl')

)

reject limit UNLIMITED;

4、建缓冲层表(S3_ods_sa4.ddl)

create table ODS_SA4

(

FA4HH INTEGER,

FA4YWLX INTEGER,

FA4BIZ INTEGER,

FA4KMH INTEGER,

FA4ZL INTEGER,

FA4KMJB INTEGER,

FA4YEF INTEGER,

FA4ZRJYE NUMBER,

FA4ZRDYE NUMBER,

FA4JRJYE NUMBER,

FA4JRDYE NUMBER,

FA4ZZJPZS INTEGER,

FA4ZZDPZS INTEGER,

FA4ZZJFSE NUMBER,

FA4ZZDFSE NUMBER,

FA4XJJPZS INTEGER,

FA4XJDPZS INTEGER,

FA4XJJFSE NUMBER,

FA4XJDFSE NUMBER,

FA4ZRHS INTEGER,

FA4JRHS INTEGER,

FA4KHS INTEGER,

FA4XHS INTEGER,

FA4LX INTEGER,

FA4QCRQ VARCHAR2(10),

FA4BY1 INTEGER,

FA4BY2 INTEGER,

FA4BY3 VARCHAR2(15),

FA4BY4 NUMBER

)

5、抽数存储过程(proc_ods_sa4.proc)

CREATE OR REPLACE procedure DATACORE.proc_ods_sa4 is

begin

DELETE FROM ods_sa4;

insert into ods_sa4

select fa4hh,

fa4ywlx,

fa4biz,

fa4kmh,

fa4zl,

fa4kmjb,

fa4yef,

fa4zrjye,

fa4zrdye,

fa4jrjye,

fa4jrdye,

fa4zzjpzs,

fa4zzdpzs,

fa4zzjfse,

fa4zzdfse,

fa4xjjpzs,

fa4xjdpzs,

fa4xjjfse,

fa4xjdfse,

fa4zrhs,

fa4jrhs,

fa4khs,

fa4xhs,

fa4lx,

FUN_IMDATE2OC(fa4qcrq),

fa4by1,

fa4by2,

fa4by3,

fa4by4

from ext_sa4;

COMMIT;

end proc_ods_sa4;