1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86
|
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/OraHome_1; export ORACLE_HOME
ORACLE_SID=RADB; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
NLS_LANG=FRENCH_BELGIUM.WE8MSWIN1252; export NLS_LANG
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data; export ORA_NLS33
# Set shell search paths:
PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/sbin:/usr/sbin:/usr/bin/x11:/usr/sbin:/etc:/usr/local/bin:/usr/X11R6/bin:/u02/oradata/home/ppas/bin;
export PATH
PATH=$PATH:$HOME/bin
export PATH
if [ $# -ne 0 ]; then
e=$1;
else
e=0
fi
#IDENTIFICATION DU JOUR DE LA SEMAINE (1...7) A LOADER
a=$(date +'%u')
b=$(date +'%Y%m%d')
#existence d'un nombre comme argument (nombre de jours en arriere)
let a=$a-$e
let s=7
echo $a
if [ $a -lt 0 ]; then
let a=$a+$s
fi
let b=$b-$e
echo "Nombre de jours avant $e"
echo "Numero du jour de la semaine loade (1...7) $a"
echo "Date du jour loade $b"
# CONTROLE DE LA PARTITION A CHARGER
sqlplus -s venant/celtel<<EOF
set timing on;
delete from sdp_data
where to_char(to_date(reportdate),'yyyymmdd') ='$b';
commit;
EOF
# Lecture du ficher à chargerr
cp ${a}_SDP1.DUMP_msisdn_unit.txt SDP$b
file=`ls SDP$b`
echo $file
cat $file | sed "s/00.$/00/; s/\./\,/g" | awk -F',' '/,/ {print ";" $1 ";" $2 ";"}' > b$file
cp b$file b_$file.txt
# Detection de la date
date=`$HOME/bin/findDate1.pl SDP$b`
# Creation du fichier de controle
echo "load data into table venant.sdp_data1 append fields terminated by ';' (" > $HOME/bin/deferred21.ctl
echo " reportdate \"to_date('$date', 'yyyy-mm-dd hh24:mi:ss')\" ," >> $HOME/bin/deferred21.ctl
echo " msisdn," >> $HOME/bin/deferred21.ctl
echo " balance \"to_number(:balance, '9999999D999999')\"" >> $HOME/bin/deferred21.ctl
echo ")" >> $HOME/bin/deferred21.ctl
# Chargement des donnees
sqlldr venant/celtel@radb data=b_$file.txt bad=$file.bad control=$HOME/bin/deferred21.ctl SILENT=(HEADER, FEEDBACK)
# Suppression des donnees fichiers temporaires
rm -f b$file
rm -f b_$file.txt
# Lecture du ficher à charger
cp ${a}_SDP2.DUMP_msisdn_unit.txt SDP2$b
file=`ls SDP2$b`
echo $file
cat $file | sed "s/00.$/00/; s/\./\,/g" | awk -F',' '/,/ {print ";" $1 ";" $2 ";"}' > b$file
cp b$file b_$file.txt
# Detection de la date
date=`$HOME/bin/findDate1.pl SDP$b`
# Creation du fichier de controle
echo "load data into table venant.sdp_data1 append fields terminated by ';' (" > $HOME/bin/deferred21.ctl
echo " reportdate \"to_date('$date', 'yyyy-mm-dd hh24:mi:ss')\" ," >> $HOME/bin/deferred21.ctl
echo " msisdn," >> $HOME/bin/deferred21.ctl
echo " balance \"to_number(:balance, '9999999D999999')\"" >> $HOME/bin/deferred21.ctl
echo ")" >> $HOME/bin/deferred21.ctl
# Chargement des donnees
sqlldr venant/celtel@radb data=b_$file.txt bad=$file.bad control=$HOME/bin/deferred21.ctl SILENT=(HEADER, FEEDBACK)
# Suppression des donnees fichiers temporaires
rm -f b$file
rm -f b_$file.txt |
Partager