nt read, write on directory RWA_FILE_CONTROL to etl_test;
grant read, write on directory RWA_FILE_LOADER to etl_test;
grant read, write on directory RWA_FILE_SH to etl_test;
grant read, write on directory RWA_FILE_BACKUP to etl_test;
f.加载数据文件
1.加载数据的表
create table F_MUREX_GL
(
data_dt DATE,
areano VARCHAR2(10),
currency VARCHAR2(10),
apcode VARCHAR2(20),
orgcde VARCHAR2(20),
damount NUMBER,
camount NUMBER,
remark VARCHAR2(1000)
);
2.加载数据的控制文件,数据文件,shell脚本,perl脚本如下
a.RWA_EDW_PLEDGE_IMPAWN_INFO.ctl -- sqlldr控制文件 目录:/ETL/control
[etl@ETL control]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.ctl
load data
TRUNCATE into table F_MUREX_GL
fields terminated by X'01'
trailing nullcols
(DATA_DT DATE'yyyy-mm-dd',AREANO,CURRENCY,APCODE,ORGCDE,DAMOUNT,CAMOUNT,REMARK)
b.RWA_EDW_PLEDGE_IMPAWN_INFO.sh -- 加载数据的shell文件 目录:/ETL/loader
[etl@ETL loader]$ more RWA_EDW_PLEDGE_IMPAWN_INFO.sh
#!/bin/sh
. /home/etl/.bash_profile
vOraPwd=$1
sqlldr userid=etl_test/$vOraPwd@ETL control=/ETL/control/RWA_EDW_PLEDGE_IMPAWN_INFO.ctl data=/ETL/data/RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt log=/ETL/log/RWA
_EDW_PLEDGE_IMPAWN_INFO.log bad=/ETL/bad/RWA_EDW_PLEDGE_IMPAWN_INFO.bad
c.RWA_EDW_PLEDGE_IMPAWN_INFO.pl -- 调用加载数据文件的shell脚本(RWA_EDW_PLEDGE_IMPAWN_INFO.sh)
#! /usr/bin/perl
########################################################
# @name :RWA_EDW_PLEDGE_IMPAWN_INFO.pl
# @parameter : db user pasaword
# @description : run RWA_EDW_PLEDGE_IMPAWN_INFO.sh and load data to table F_MUREX_GL
#
# @create_date :2015-02-09
# @author :Tux
# @version :1.0.0
# @source :
# @target :
# @modify :
# @copyright :
####################################################################
use strict;
my $passwd;
my $clm_shell = '/ETL/loader/RWA_EDW_PLEDGE_IMPAWN_INFO.sh';
$passwd = $ARGV[0];
# run shell script
eva l {
system("sh $clm_shell $passwd");
};
if ($@ ne '') {
die "execute sqlldr script failed\n";
}
else
{
print "the sqlldr script run sucessessfull !!\n";
}
d.RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt -- 数据文件
[etl@ETL data]$ more RWA_EDW_RWA_PLEDGE_IMPAWN_INFO_20140630_001.txt
2014-06-30^^^^
2014-06-30 00350AED0232 6114 0.000 1000000.000
2014-06-30 00350AUD0148 6107 4538300.000 0.000
2014-06-30 00350AUD0110 6107 1526300.000 0.000
2014-06-30 00350AUD0971 6107 8006100.000 0.000
2014-06-30 00350AUD0158 6107 154.430 0.000
2014-06-30 00350AUD5497 6108 0.000 15200.000
2014-06-30 00350AUD0155 6108 1000000.000 0.000
2014-06-30 00350CAD0239 6107 0.000 950000.000
2014-06-30 00350CAD0247 6107 0.000 950000.000
2014-06-30 00350CAD9317 6107 262222.000 0.000
2014-06-30 00350CAD0123 6114 1000000.000 0.000
2014-06-30 00350CHF0971 6107 2383200.000 0.000
2014-06-30 00036CNY9867 6118 572590.240 0.000
2014-06-30 00350CNY7066 6118 9000000.000 0.000
2014-06-30 00036CNY7048 6118 7546536516.090 0.000
2014-06-30 00036CNY6814 6118 1323765.700 0.000
2014-06-30 00350CNY5512 6107 0.000 1089729877.740
2014-06-30 00350CNY9861 6118 248471.230 0.000
2014-06-30 00350CNY9887 6118 2666.660 0.000