|
|
PROBLEM WITH ORACLE QUERY IN UNIX SCRIPT
06-24-2011
#!/bin/sh
###########################################
# #
# THIS SCRIPT FETCHES THE INFORMATION #
# FROM THE INFORMATICA LOG FILE AND #
# LOADS THE DATA IN TO THE SESS_LOG_TAB #
# #
###########################################
find /export/home/pmutv/data/ -type f -name *.4 -print > /export/home/pmutv/auto/LogFiles.txt
for i in `cat /export/home/pmutv/auto/LogFiles.txt | cut -f6 -d"/"`
do
SESSION_NAME=`cat /export/home/pmutv/data/$i | head -1 | awk '{print $3}' | cut -f2 -d"[" |cut -f1 -d"]"`
WORKFLOW_NAME=`grep -w Workflow /export/home/pmutv/data/$i | awk '{print $4}' | cut -f2 -d"[" | cut -f1 -d"]"`
START_TIMESTAMP=`grep -iw "load start time" /export/home/pmutv/data/$i | cut -c 18-41 | cut -f2,3,5 -d" " | sed 's/ //g'`
SESSION_STATUS=`cat /export/home//pmutv/data/$i | tail -1 | awk '{print $5}'`
OUTPUT=`grep -iw "output rows" /export/home/pmutv/data/$i | head -1 | cut -f1 -d"," | cut -f4 -d" " | cut -f2 -d"[" | cut -f1 -d"]"`
REJECTED=`grep -iw "output rows" /export/home/pmutv/data/$i | head -1 | cut -f1 -d"," | cut -f4 -d" " | cut -f2 -d"[" | cut -f1 -d"]"`
start_date=$START_TIMESTAMP
New_date=`nawk -v Date=${start_date} '
BEGIN { Months=" JanFebMarAprMayJunJulAugSepOctNovDec"
year = substr(Date, 6, 4) + 0;
day = substr(Date, 4, 2) + 0;
month = index(Months, substr(Date, 1, 3))/3;
printf "%04d%02d%02d", year, month, day;
exit;
}
'`
if [ $SESSION_STATUS != "completed" ]; then
SESSION_STATUS="Failed"
else
SESSION_STATUS="Success"
fi
echo "Session Name------> $SESSION_NAME"
echo "Workflow name-----> $WORKFLOW_NAME"
echo "Load Start Time---> $New_date "
echo "Session Status----> $SESSION_STATUS"
echo "Inserts ----------> $OUTPUT"
echo "Rejects-----------> $REJECTED"
'sqlplus -s "pmutv/pmutv1" << EOFSQL
set head off
insert into sess_log values('$SESSION_NAME','$WORKFLOW_NAME','$New_date','$SESSION_STATUS','$OUTPUT','$REJECTED');
exit;
EOFSQL'
echo "$?"
done
06-24-2011
|
|