Code:
#!/usr/bin/ksh
# 1. part start #############################################################################
############################## procedures, utilites ##############################
#--------------------------------------------#
env_back() {
# set back NLS settings
export NLS_NUMERIC_CHARACTERS=$vOldNlsNumChar
export NLS_LANG=$vOldNlsLang
}
#--------------------------------------------#
update_group() {
# update record in LOADEDMETAFILESGROUPS
echo "Update log record in LOADEDMETAFILESGROUPS - calling sqlpus"
echo "dbg execute Loader_Utils.updt_end_lmg ('$pGroupName', '$1', $pDeltaIndicator)"
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $pSqlConStr
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********* Start of Group Update part *********
PROMPT
PROMPT Loader_Utils.updt_end_lmg ('$pGroupName', '$1', $pDeltaIndicator)
execute Loader_Utils.updt_end_lmg ('$pGroupName', '$1', $pDeltaIndicator)
commit;
PROMPT
PROMPT ********* End of Group Update part *********
PROMPT
exit
EOF_XTBS
if [ $? = 1 ] || [ $? = 2 ]; then
echo
echo "!!! Oracle error occured! Check sql log file "$vSqlLogFile"!!!"
echo "!!! Script terminated !!!"
echo
# set back NLS settings
env_back
exit 101
fi
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
echo
echo "!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
echo "!!! Script terminated !!!"
echo
# set back NLS settings
env_back
exit 102
fi
if [ -n "$(echo `grep "Contract/Partner/Claim ID missing !!!" $vSqlLogFile`)" ]; then
echo
echo "!!! Logical error - Contract/Partner/Claim ID missing !!! Check LoadRecords table !!!"
# set back NLS settings
env_back
exit 103
fi
echo "LOADEDMETAFILESGROUPS record updated - exit sqlplus"
}
#--------------------------------------------#
err_upd_goup_exit() {
echo "!!! Script terminated !!!"
echo
# update record in LOADEDMETAFILESGROUPS
update_group "error"
# set back NLS settings
env_back
exit 0
}
#--------------------------------------------#
update_file_log() {
# secound sqlplus part
# call sqlplus, log record in LOADEDMETAFILES
# with information readed from loader log file ...
# if eny error, exit slqpus and return 1 or 2
# log of sql output is in *_sql.log file
echo "Loader start - Log to LOADEDMETAFILES - calling sqlpus"
# 1. part end #############################################################################
# 2. part start #############################################################################
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $pSqlConStr
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********* Start of Update part *********
PROMPT $vFileNo. flat file - $vFlatFile
PROMPT
PROMPT Loader_Utils.updt_end_lmf($vLmfId, '$vLdrStatus', $vLdrErrRecTab, $vLdrSuccRec, substr('$vLdrOraErr',1,4000));
execute Loader_Utils.updt_end_lmf($vLmfId, '$vLdrStatus', $vLdrErrRecTab, $vLdrSuccRec, substr('$vLdrOraErr',1,4000));
commit;
PROMPT ********* End of Update part *********
PROMPT $vFileNo. flat file - $vFlatFile
exit
EOF_XTBS
if [ $? = 1 ] || [ $? = 2 ]; then
echo
echo "!!! Oracle error occured! Check sql log file "$vSqlLogFile"!!!"
err_upd_goup_exit
fi
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
echo
echo "!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
err_upd_goup_exit
fi
echo "Loader end - Log to LOADEDMETAFILES, sql log - "$vSqlLogFile" - exit sqlplus"
echo
}
#--------------------------------------------#
#######################################################################
############################## main part ##############################
#######################################################################
# assign parametres
pSqlConStr=$1
pGroupName=$2
pSourceConStr=$3
pDeltaIndicator=$4
# constants
cLinePrefix="#FlatFiles"
cParamSep=" "
cMainPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1"
cGenPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1/files/working"
# parameters
vGroupStatus="error"
# get script name
vScriptPathName=`pwd`"/"$0
vScriptName=`echo $vScriptPathName | awk '{len=split($0,a,"/"); print a[len]}'`
vScriptPathName=`pwd`
# get sql log file name
vSqlLogFile=$pGroupName"_sql.log"
# check the parameter, if not correct message and exit script
if [ "$pFileName" = "help" ] || [ "$pFileName" = "-h" ] || [ "$pFileName" = "/h" ]; then
echo
echo "Usage: $cFileName [oracle connect string] [loader group name] [delta]"
echo "(e.g. normal load: $cFileName scott/tiger@orcl group_1"
echo " delta load: $cFileName scott/tiger@orcl group_1 delta)"
echo
exit 111
fi
if [ -n "$pDeltaIndicator" ] && [ "$pDeltaIndicator" != "delta" ]; then
echo
echo "Usage: $cFileName [oracle connect string] [loader group name] [delat indicator - optional]"
echo "(e.g. $cFileName scott/tiger@orcl group_1 delta)"
echo
exit 112
fi
if [ "$pDeltaIndicator" = "delta" ]; then
pDeltaIndicator="true"
else
pDeltaIndicator="false"
fi
echo
echo "*************** Start - "$vScriptName" ***************"
echo
# 2. part end #############################################################################
# 3. part start #############################################################################
# set LNS settings for this script
vOldNlsNumChar=$NLS_NUMERIC_CHARACTERS
vOldNlsLang=$NLS_LANG
export NLS_NUMERIC_CHARACTERS=',.'
export NLS_LANG='AMERICAN_AMERICA.WE8ISO8859P1'
# first sqlplus part
# call sqlplus, insert log record in table LOADEDMETAFILESGROUPS
# if eny error, exit slqpus and return 1 or 2
# log of sql output is in [loader group name]_sql.log file
echo "Start of Group read and Group log - calling sqlpus"
echo "Generating Controll Files"
sqlplus -s /nolog <<EOF_XTBS >$vSqlLogFile 2>&1
connect $pSqlConStr
set serveroutput on
set linesize 250
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT *************** Start of Group read and Group insert log ***************
PROMPT
PROMPT Loader_Utils.GET_LOADER_CONFIG('$cLinePrefix', '$cParamSep', '$pGroupName', '1370');
execute Loader_Utils.GET_LOADER_CONFIG('$cLinePrefix', '$cParamSep', '$pGroupName', '1370');
commit;
PROMPT *************** End of Group Insert part ***************
PROMPT
exit 0
EOF_XTBS
# if oracle or system error display message
if [ $? = 1 ] || [ $? = 2 ]; then
echo
echo "!!! Oracle error occured! Check sql log file $vSqlLogFile !!!"
err_upd_goup_exit
fi
# check oracle login errors
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
echo
echo "!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
err_upd_goup_exit
fi
echo "Parameters read output is in "$vSqlLogFile" - exit sqlplus"
echo
if [ ! -n "$(echo `grep -E $cLinePrefix $vSqlLogFile`)" ]; then
echo
echo "!!! No such a group or no files in this group!!!"
echo "!!! Check views LOADER_CFG_GROUPS, LOADER_CFG_FILES for group "$pGroupName" !!!"
echo "!!! Script terminated !!!"
echo
# set back NLS settings
env_back
exit 121
fi
# copy generated controll files to subfolders
# information is from sql output file
echo "Coping Controll Files to 'caller' subdirectory"
if [ $PWD != $cGenPath ]; then
grep -E $cLinePrefix $vSqlLogFile | while IFS= read vLine
do
set -- $vLine
vCtlFile=$5
vBadCtlFile=$6
mv $cGenPath'/'$vCtlFile $PWD
mv $cGenPath'/'$vBadCtlFile $PWD
done
fi
# 3. part end #############################################################################
# 4. part start #############################################################################
vFileNo=$((1))
# call loader for each flat file #############################################
# information is from sql output file
while IFS= read vLine
do
set -- $vLine
if [ "$1" != "$cLinePrefix" ]; then
continue
fi
vPartNo=$2
vLmfId=$(($3))
vFlatFile=$4
vCtlFile=$5
vBadCtlFile=$6
vRecordCount=$(($7))
vGroupStatus="error"
echo "********** Looping - "$vFileNo". flat file - "$vFlatFile" **********"
echo
if [ ! -n "$vPartNo" ]; then # check partition no
echo
echo "!!! Error partition no not exists !!!"
err_upd_goup_exit
fi
if [ ! -n "$vLmfId" ]; then # check lmf_id
echo
echo "!!! Error LMF_ID not exists !!!"
err_upd_goup_exit
fi
if [ ! -f "$vFlatFile" ]; then # check flat file
echo
echo "!!! Error flat file $vFlatFile not exists !!!"
err_upd_goup_exit
fi
if [ ! -f "$vCtlFile" ]; then # check controll file
echo
echo "!!! Error controll file $vCtlFile not exists !!!"
err_upd_goup_exit
fi
if [ ! -n "$vRecordCount" ]; then # records count
echo
echo "!!! Error parameter record count not exists !!!"
err_upd_goup_exit
fi
echo "Loader start - Log to LOADEDMETAFILES - calling sqlpus"
# 4. part end #############################################################################
# 5. part start #############################################################################
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $pSqlConStr
set serveroutput on
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********** Insert part **********
PROMPT $vFileNo. flat file - $vFlatFile
PROMPT
PROMPT Loader_Utils.updt_start_lmf($vLmfId, '$vScriptPathName');
execute Loader_Utils.updt_start_lmf($vLmfId, '$vScriptPathName');
commit;
PROMPT ********** End of Insert part **********
PROMPT $vFileNo. flat file - $vFlatFile
PROMPT
exit 0
EOF_XTBS
# if oracle or system error display message
if [ $? = 1 ] || [ $? = 2 ]; then
echo
echo "!!! Oracle error occured! Check sql log file "$vSqlLogFile" !!!"
err_upd_goup_exit
fi
# check oracle login errors
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
echo
echo "!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
err_upd_goup_exit
fi
echo "Loader start - Log to LOADEDMETAFILES, sql log - "$vSqlLogFile" - exit sqlplus"
echo
vLdrLogFile=`echo $vCtlFile | awk '{sub(".ctl",""); print}'`
vLdrBadFile=$vLdrLogFile".bad"
vLdrLogFile=$vLdrLogFile".log"
# call sql loader
# log of sql output is in *_ldr.log file
# bad rows are in *_ldr.bad file
echo "Run SQL loader - loading flat file "$vFlatFile
#echo "<dbg>" sqlldr userid=$pSqlConStr data=$vFlatFile control=$vCtlFile log=$vLdrLogFile bad=$vLdrBadFile rows=1000 parallel=true
#echo "<dbg> PWD="$PWD
#ls -al >test_dir.txt
/usr/bin/ksh sqlldr rows=1000 userid=$pSourceConStr data=$vFlatFile control=$vCtlFile log=$vLdrLogFile bad=$vLdrBadFile >sqlldr_log.log
# parallel=true silent=all
#echo "sqlldr return param="$?
echo
echo "Output is in file "$vLdrLogFile
if [ -f "$vLdrBadFile" ]; then
echo "Bad records are in file "$vLdrBadFile
fi
echo
vLdrSuccRec=$(echo `grep "successfully loaded" $vLdrLogFile` | awk -F" " '{print $1}')
vLdrReadRec=$(echo `grep "Total logical records read:" $vLdrLogFile` | awk -F" " '{print $5}')
[ "$vLdrReadRec" = "" ] && vLdrReadRec=0
# check loader log file for errors and info about inserted, rejected, ... records
vLdrOraErr=$(echo `grep "ORA-" $vLdrLogFile`)
vLdrOraErr=$(echo $vLdrOraErr | cut -c1-2000)
if [ "$vLdrOraErr" != "" ]; then
echo "!!! Oracle error occured! Check loader log file "$vLdrLogFile" !!!"
echo
fi
vLdrErrRecTab=$(echo `grep "not loaded due to data errors." $vLdrLogFile` | awk -F" " '{print $1}')
vLdrErrRecAll=$(echo `grep "not loaded due to data errors." $vLdrLogFile` | awk -F" " '{print $9}')
[ "$vLdrErrRecTab" = "" ] && vLdrErrRecTab=0
[ "$vLdrErrRecAll" = "" ] && vLdrErrRecAll=0
vLdrErrRec=$(($vLdrErrRecTab+$vLdrErrRecAll))
echo "Expected record count: " $vRecordCount
echo
echo "Read records: " $vLdrReadRec
if [ "$vLdrErrRecTab" != 0 ] || [ "$vLdrErrRecAll" != 0 ]; then
echo "Meta table errors: " $vLdrErrRecTab
echo "Errors LOADEDRECORDS: " $vLdrErrRecAll
echo "All errors: " $vLdrErrRec
else
echo "Errors: " $vLdrErrRecTab
fi
echo "Suuccesfully loaded: " $vLdrSuccRec
echo
if [ ! -n "$(echo `grep "MAXIMUM ERROR COUNT EXCEEDED" $vLdrLogFile`)" ]; then
vLdrStatus="finished"
else
vLdrStatus="aborted"
echo "!!! Loader aborted ! Check loader log file "$vLdrLogFile" !!!"
echo
update_file_log
err_upd_goup_exit
fi
if [ "$vLdrReadRec" != "$vRecordCount" ]; then
vLdrStatus="error"
echo "!!! Flat file error ! File "$vFlatFile" has different count of records than expected !!!"
echo "!!! Check "$vFlatFile" and loader log file "$vLdrLogFile" !!!"
echo
update_file_log
err_upd_goup_exit
fi
# 5. part end #############################################################################
# 6. part start #############################################################################
update_file_log
if [ "$vLdrStatus" != "finished" ]; then
err_upd_goup_exit
fi
# end of loading flat file
echo "********** End - load "$vFileNo". flat file - "$vFlatFile" **********"
echo
vFileNo=`expr $vFileNo + 1`
vGroupStatus="OK"
done < $cGenPath"/"$vSqlLogFile
echo vGroupStatus=$vGroupStatus
# update record in LOADEDMETAFILESGROUPS
update_group $vGroupStatus
# set back NLS settings
env_back
echo
echo "*************** End - "$vScriptName" ***************"
echo
exit 0
############################## end :) ##############################
# 6. part end #############################################################################