03-19-2012
exact problem with scripts
Hi,
and thanks for trying to help...
I forget to mention that this script working on Oracle Linux what is RHEL 4 but not working on AIX.
The exact situation is: I am trying to load some flatfiles to Oracle.
The shell call is form DB from Java procedure.
The Java procedure picked up the return value and the display (echo-s from display).
Now my problem is when the program sqlldr is called than I did not get the echo-s and the return value. I have all echo-s still the sqlldr call.
Mroki
PL/SQL call
Code :
begin
dbms_output.put_line(osexec('/dts/sh/start_sh_script.sh /dts/loader/Canal_Test_01/v1/bin/load_migrate_part.sh'));
end;
calling script:
start_sh_script.sh
Code :
#!/usr/bin/ksh
# ==============================================================================
# Start/Log SH Script
# ==============================================================================
# Parameters
#. $HOME/.profile
. /app/ICSASC00/dts/sh/params.sh
cd $DTS_SH_DIR
LogDir=$DTS_SH_LOG
# log each execution
ThisScriptFullName="$0"
ThisScriptName=`basename $0`
ThisScriptName=${ThisScriptName%.*}
ThisScriptDir=`dirname $0`
ThisScriptLogFullName="$LogDir/$ThisScriptName.log"
#echo ThisScriptLogFullName=$ThisScriptLogFullName
Now=$(date +"%Y_%m_%d_%H_%M_%S")
echo "Execution begin at $Now" >>$ThisScriptLogFullName
RunScriptName=`basename $1`
RunScriptName2=${RunScriptName%.*}
# create log file for each execution
LogName=$RunScriptName2"_"$Now".log"
#echo LogName=$LogName
LogFullName="$LogDir/$LogName"
echo "%LogFullName%"=$LogFullName
echo "$1 $2 $3 $4 $5 $6 $7 $8" >>$ThisScriptLogFullName
echo "log: $LogFullName" >>$ThisScriptLogFullName
$1 $2 $3 $4 $5 $6 $7 $8 >>$LogFullName
echo "%Return_Prameter%"=$?
echo "%Return_Prameter%"=$? >>$ThisScriptLogFullName
Now=$(date +"%Y_%m_%d_%H_%M_%S")
echo "End at $Now" >>$ThisScriptLogFullName
exit $?
1. script - load_migrate_part.sh
Code :
#!/usr/bin/ksh
# 1. part start #############################################################################
# =============================================================================
# 2005.02.04 L.Lelkes created
# =============================================================================
############################## procedures, utilites ##############################
#--------------------------------------------#
echo_file_disp() {
# set back NLS settings
echo $gEchoText >>$cLogFile
}
#--------------------------------------------#
#######################################################################
############################## main part ##############################
#######################################################################
# constants
cKontFile="*.ktr"
cTmpKontFileList="tmp_kont_file_list.tmp"
cSqlLogFileExt=".prep.sql.log"
cLoaderLogExt=".ldr.log"
cSqlConStr="dtsloader/dtsloader@icsasc00"
cSourceConStr="dts_source1/dts_source1@icsasc00"
cPartKey=10001
# paths
cMainPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1"
cInPath="/app/ICSASC00/dts/loader/Canal_Test_01/inbox"
cWorkingPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1/files/working"
cArchivPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1/files/archiv"
cErrorPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1/files/error"
cBinPath="/app/ICSASC00/dts/loader/Canal_Test_01/v1/bin"
# gennerate log file name
cLogDirDate=`date +"%Y%m%d%H%M"`
cLogFile=$cWorkingPath"/load_migrate_part.log"
# begin
gEchoText="Begin load_migrate_part.sh "`date +"%Y%m%d%H%M"`
echo_file_disp
# set oracle enviroment variables
vOldNlsNumChar=$NLS_NUMERIC_CHARACTERS
vOldNlsLang=$NLS_LANG
export NLS_NUMERIC_CHARACTERS=',.'
export NLS_LANG='AMERICAN_AMERICA.WE8ISO8859P1'
gEchoText="Changeing directory to "$cInPath" - creating list of kontroll files"
echo_file_disp
cd $cInPath
vKontFileExists="false"
vLoadWithError="false"
# -- get the list of controll files
ls ${cKontFile} >$cWorkingPath"/"$cTmpKontFileList
gEchoText=`cat $cWorkingPath"/"$cTmpKontFileList`
echo_file_disp
# loop thrue the list of kontroll files
while read vLine1
do
# error flag init. from inside loops to idetify after inside loop 'break' (error) to make continue
# (go for a next record in one level higher loop)
vErrOcc="false"
# check existenz of controll files - in loop -> exists
vKontFileExists="true"
gEchoText="Changeing directory to "$cInPath" - loop thrue the list of kontroll files"
echo_file_disp
cd $cInPath
# get - file name = group name
vKtrFileName=`echo $vLine1 | awk '{len=split($0,a,"/"); print a[len]}'`
vGroupName=$vKtrFileName"."$cLogDirDate
vSqlLogFile=$cWorkingPath"/"$vGroupName$cSqlLogFileExt
gEchoText="Kontroll File: "$vLine1" - moving to working directory"
echo_file_disp
mv $vKtrFileName $cWorkingPath
# set variable for first run
vSystName="start"
# get system name and controll inconsistency of system name
cat $cWorkingPath"/"$vLine1 | while read vLine2
do
set -- $vLine2
gEchoText="Moveing file "$1" to "$cWorkingPath
echo_file_disp
mv $1 $cWorkingPath
if [ "$vSystName" = "start" ]; then
vSystName=$3
else
if [ "$vSystName" != "$3" ]; then
gEchoText="Inconsistency in kontroll file, more than one system inside "$vGroupName" and "$3
echo_file_disp
vErrOcc="true"
break
fi
fi
done
# 1. part end #############################################################################
# 2. part start #############################################################################
# check for error - if error go for the next kontroll file
if [ "$vErrOcc" = "true" ]; then
gEchoText="Moving files to "$cErrorPat
echo_file_disp
mv * $cErrorPath
continue
fi
gEchoText="Changeing directory to "$cWorkingPath
echo_file_disp
cd $cWorkingPath
gEchoText="Insert record to LOADEDMETAFILESGROUPS - goup "$vGroupName" - call sqlplus"
echo_file_disp
# ----------------------- sqlplus call insert LOADEDMETAFILESGROUPS -----------------------
sqlplus -s /nolog <<EOF_XTBS >$vSqlLogFile 2>&1
connect $cSqlConStr
set serveroutput on
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********** Insert LOADEDMETAGROUPS part **********
PROMPT $vGroupName
PROMPT
PROMPT loader_utils.Insert_LMG('$vGroupName', '1370', '$vSystName', $cPartKey);
execute loader_utils.Insert_LMG('$vGroupName', '1370', '$vSystName', $cPartKey);
commit;
PROMPT ********** End of Insert LOADEDMETAGROUPS part **********
PROMPT
exit 0
EOF_XTBS
# if oracle or system error display message
if [ $? = 1 ] || [ $? = 2 ]; then
gEchoText="!!! Oracle error occured! Check sql log file "$vSqlLogFile" !!!"
echo_file_disp
mv * $cErrorPath
continue
fi
# check oracle login errors
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
gEchoText="!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
echo_file_disp
mv * $cErrorPath
continue
fi
gEchoText="Record inserted to LOADEDMETAFILESGROUPS - goup "$vGroupName" - exit sqlplus"
echo_file_disp
#loop thrue kontroll file lines
cat $vLine1 | while read vLine2
do
set -- $vLine2
vTextFile=$1
vMetaTable=$2
vRecCount=$4
gEchoText="Insert record to LOADEDMETAFILES - "$vTextFile" - call sqlplus"
echo_file_disp
# 2. part end #############################################################################
# 3. part start #############################################################################
# ----------------------- sqlplus call insert LOADEDMETAFILES -----------------------
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $cSqlConStr
set serveroutput on
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********** Insert LOADEDMETAFILES part **********
PROMPT $vLine2
PROMPT
PROMPT loader_utils.Insert_LMF('$vGroupName', '$vTextFile', '$vMetaTable', '$vRecCount');
execute loader_utils.Insert_LMF('$vGroupName', '$vTextFile', '$vMetaTable', '$vRecCount');
commit;
PROMPT ********** End of Insert LOADEDMETAFILES part **********
PROMPT
exit 0
EOF_XTBS
# if oracle or system error display message
if [ $? = 1 ] || [ $? = 2 ]; then
gEchoText="!!! Oracle error occured! Check sql log file "$vSqlLogFile" !!!"
echo_file_disp
vErrOcc="true"
break
fi
# check oracle login errors
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
gEchoText="!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
echo_file_disp
vErrOcc="true"
break
fi
gEchoText="Record inserted to LOADEDMETAFILES - exit sqlplus"
echo_file_disp
done
# check for error - if error go for the next kontroll file
if [ "$vErrOcc" = "true" ]; then
gEchoText="Moving files to "$cErrorPat
echo_file_disp
mv * $cErrorPath
continue
fi
# ----------------------- sqlldr call loading -----------------------
gEchoText="Load group $vGroupName"
echo_file_disp
vLoaderLog=$vGroupName$cLoaderLogExt
$cBinPath"/"meta_load.sh $cSqlConStr $vGroupName $cSourceConStr #>>$cLogFile
# ----------------------- sqlplus call check loading result -----------------------
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $cSqlConStr
set serveroutput on
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********** Check loading result **********
PROMPT $vGroupName
PROMPT
PROMPT loader_utils.Check_Res_Upd_LMG('$vGroupName');
execute loader_utils.Check_Res_Upd_LMG('$vGroupName');
commit;
PROMPT ********** End of Check loading result **********
PROMPT
exit 0
EOF_XTBS
# if oracle or system error display message
if [ $? = 1 ] || [ $? = 2 ]; then
gEchoText="!!! Oracle error occured! Check sql log file "$vSqlLogFile" !!!"
echo_file_disp
continue
fi
# check oracle login errors
if [ -n "$(echo `grep "Not connected" $vSqlLogFile`)" ]; then
gEchoText="!!! Oracle Login error !!! Check "$vSqlLogFile" !!!"
echo_file_disp
continue
fi
# read result form sqllog
vLdrResult=$(echo `grep "#"$vGroupName $vSqlLogFile` | awk -F" " '{print $2}')
if [ "$vLdrResult" = "loaded_ok" ]; then
gEchoText="Moving files to archiv folder."
echo_file_disp
mkdir $cArchivPath"/"$cLogDirDate
mv * $cArchivPath"/"$cLogDirDate
vLogDirForLMG=$cArchivPath"/"$cLogDirDate
gEchoText="Loading OK - group "$vGroupName" !!!"
echo_file_disp
else
gEchoText="Moving files error folder."
echo_file_disp
mkdir $cErrorPath"/"$cLogDirDate
mv * $cErrorPath"/"$cLogDirDate
vLogDirForLMG=$cErrorPath"/"$cLogDirDate
vLoadWithError="true"
if [ "$vLdrResult" = "loaded_ok" ]; then
gEchoText="!!! Loading OK migrating with Errors - group "$vGroupName" !!!"
else
gEchoText="!!! Loading with errors - group "$vGroupName" !!!"
fi
echo_file_disp
continue
fi
# 3. part end #############################################################################
# 4. part start #############################################################################
# ----------------------- sqlplus save log dir -----------------------
sqlplus -s /nolog <<EOF_XTBS >>$vSqlLogFile 2>&1
connect $cSqlConStr
set serveroutput on
set SERVEROUTPUT ON SIZE 100000
whenever sqlerror exit 1
whenever oserror exit 2
PROMPT
PROMPT ********** Check loading result **********
PROMPT $vGroupName
PROMPT
PROMPT loader_utils.Upd_LMG_Logdir('$vGroupName', '$vLogDirForLMG');
execute loader_utils.Upd_LMG_Logdir('$vGroupName', '$vLogDirForLMG');
commit;
PROMPT ********** End of Check loading result **********
PROMPT
exit 0
EOF_XTBS
#loop thrue kontroll file lines - end
done < $cWorkingPath"/"$cTmpKontFileList
gEchoText="End load_migrate_part.sh "`date +"%Y%m%d%H%M"`
echo_file_disp
export NLS_NUMERIC_CHARACTERS=$vOldNlsNumChar
export NLS_LANG=$vOldNlsLang
# return parameter
if [ "$vKontFileExists" != "true" ]; then
# no kontroll file in inbox
gEchoText="return 10 "
echo_file_disp
exit 10
fi
if [ "$vLoadWithError" = "true" ]; then
# if there is error betweel load return 20
gEchoText="return 20"
echo_file_disp
exit 20
fi
gEchoText="return 0"
echo_file_disp
gEchoText="----------------------------------------------------------------------"
echo_file_disp
exit 0
# 4. part end #############################################################################
2. script - meta_load.sh
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 #############################################################################
Last edited by Scott; 03-19-2012 at 05:24 PM ..
Reason: Please use CODE tags for CODE
10 More Discussions You Might Find Interesting
1. AIX
HI
I am trying to write a shell script which accepts two strings from user and finds the matching number of characters in those two
ex:
string 1 abcd
string 2 bcgt
result is bc (matching characters)
pls provide me with a basic script for doing that (1 Reply)
Discussion started by: akmtcs
1 Replies
2. Shell Programming and Scripting
Hi All,
I have this script which doesn't work on AIX ksh shell..
# ! /usr/bin/ksh
grep -irl "6000" /home/applmgr/xyz > file_list_port.log
xargs sed -i 's/6000/6010/g' < file_list_port.log
But this same script has worked perfectly on linux bash shell..
Could anyone please share... (4 Replies)
Discussion started by: a1_win
4 Replies
3. Shell Programming and Scripting
Hello all,
I am currently try to learn the linux operating system as well as some bash programming. I have come across some online course work which has been very helpful, I have been working through some assignments and since I have no teacher to ask I have come to you experts.
So the... (6 Replies)
Discussion started by: g2axiom
6 Replies
4. Shell Programming and Scripting
I have a script that "runs" a script. For example:
./runscript.ksh pcnmc01.ksh
runscript puts pcnmc01.ksh into the background with log output going to the logfile.
After executing the command, I get this output:
Running script in the background: pcnmc01.ksh
Logfile:... (2 Replies)
Discussion started by: Eben Yong
2 Replies
5. Programming
hii friends i m fairy new to c programming.can any one suggest some good websites and some good books for beginner (6 Replies)
Discussion started by: pankajchandel
6 Replies
6. AIX
Hello,
For some time I have intellistation 9111-285 and I installed AIX 6.1 on it. As a complete beginner I have 2 questions in general about AIX and two specific:
1. is the SMS (system management services) part of AIX? As I noticed when I had Yellowdog Linux installed they weren't available?... (2 Replies)
Discussion started by: kenashkov
2 Replies
7. Shell Programming and Scripting
I started to learn KSH
I am doing some exercises from the book
$ umask =rx, u+w
$ print Keep track of everythine > file1.out
$ print Be careful >> file1.out
ksh: file1.out: cannot create
$ umask -S ... (1 Reply)
Discussion started by: solaris_user
1 Replies
8. Shell Programming and Scripting
Hi all,
I'm a fairly new beginner with shell programming and python programming. I have a mac (mountain lion OS 10.8.2) and use the terminal for programming. I'm trying to use the unix to easily organize some language data that I am working with. Basically I have to word lists, that I need to... (2 Replies)
Discussion started by: nomadblue
2 Replies
9. Shell Programming and Scripting
So guys basically I was really sick and couldn't attend the labs and lectures and I went to my lecture hoping he would say ok I will help you from the start but he just said google it. So If it's possible to make the assignment and explain more in detail why is that would be really helpfull.
I can... (1 Reply)
Discussion started by: Joola94
1 Replies
10. Shell Programming and Scripting
My variable contains the following string
I wish to replace \n with "space" so the expected output is:
I understand that the /n is not a new linein this case.
I'm on AIX using ksh shell. Below is all that I tried.
echo $str | sed -e "s#\n# #g";
echo $str | sed -e "s#\n#' '#g";... (5 Replies)
Discussion started by: mohtashims
5 Replies