Hi,

I have a case where i am connecting to the oracle DB and reading a column value....
Script is in production...it was running fine,,,but suddenly now some times its started giving pain....
Script runs dailly....but sometimes its reading data from Oracle DB and sometimes its not rading the Data,,,
ID PASSWORD TO CONNECT TO THE DB IS CORRECT ONLY....
cAN ANYBODY TELL THE SOLUTION FOR THIS?

Please share your codes...

HOpe anyone one of us can help you

THanks
SHa

#!/usr/bin/ksh
PROG=`basename ${0}`
SHL_DIR=`dirname ${0}`
EXIT_STATUS=0
ParameterFile="CbsCollPay_static.ini"
#
# BINPATH="/opt/app/dstage/Ascential/DataStage/DSEngine/bin"
BINPATH=${DS_HOME}/bin
#
RUNSCRIPTPATH="/EVF/work_08/script/"
#
# ORACLE_HOME="/etl01/opt/app/oracle/product/8.1.7/"
# export ORACLE_HOME
# ORAPATH="/etl01/opt/app/oracle/product/8.1.7/bin"
RUNBATCHNAME="Batch::CBSCollPayMaster"
PROJDIR="/EVF/work_08/"
BATCHNAME="LP/COLLPAY/CBS"
INIDIR="ini"
LOGDIR="/EVF/work_08/LP/COLLPAY/CBS/log" #CHANGE#
X=0
BATCHTIMESTAMP=`date +"%Y-%m-%d-%H.%M.%S"`
echo "${BATCHTIMESTAMP} ${PROG} Initialization..."
LOGFILE="${LOGDIR}/CbsCollPay_${BATCHTIMESTAMP}.log" #CHANGE#
if [ ! -r ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} ]; then
error_ msg="${BATCHTIMESTAMP} ${PROG} Failed to read parameter file ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile}"
break
fi
set -x
# Look in the parameters.ini file to determine the directory paths
ICVDB=`grep "ICVDB=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
ICVDBUSER=`grep "ICVDBUSER=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
ICVDBPASSWORD=`grep "ICVDBPASSWORD=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile}|cut -d "=" -f2`
SRCDIR=`grep "SRCDIR=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
PROJDIR=`grep "PROJDIR=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
SCRIPTDIR=`grep "SCRIPTDIR=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
ARCHDIR=`grep "ARCHDIR=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
FTPSERVERNAME=`grep "FTPSERVERNAME=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
FTPUSERNAME=`grep "FTPUSERNAME=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
FTPPASSWORD=`grep "FTPPASSWORD=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
REMOTEDIR=`grep "REMOTEDIR=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
SRCFILENAME=`grep "SRCFILENAME=" ${PROJDIR}${BATCHNAME}/${INIDIR}/${ParameterFile} | cut -d "=" -f2`
PREFILE=`ls ${PROJDIR}${BATCHNAME}/${ARCHDIR}/`
cd ${PROJDIR}${BATCHNAME}/${SRCDIR}/
chmod 775 *.*
if [ ! -f ${PROJDIR}${BATCHNAME}/${SRCDIR}/${PREFILE} ]; then
mv ${PROJDIR}${BATCHNAME}/${SRCDIR}/${PREFILE} ${PROJDIR}${BATCHNAME}/${ARCHDIR}/
fi
EXIT_STATUS=0
echo "ICVDB=$ICVDB" >> ${LOGFILE}
echo "ICVDBUSER=$ICVDBUSER" >> ${LOGFILE}
echo "ICVDBPASSWORD=$ICVDBPASSWORD" >> ${LOGFILE}
#
# /etl01/opt/app/oracle/product/8.1.7/bin/sqlplus ... ... #
#
X=`${ORACLE_HOME}/bin/sqlplus -silent $ICVDBUSER/$ICVDBPASSWORD@$ICVDB <<END
set pagesize 0 feedback off verify off heading off echo off
select cast((substr(max(a.LAST_FILE_NAME),10,LENGTH(max(Trim(a.LAST_FILE_NAME)))-13)) as integer) from DA.ETL_CONTROL
a where a.LAST_POST_DTS in(
select max(b.LAST_POST_DTS) from DA.ETL_CONTROL b where b.TABLE_NAME='CBS_COLL_PAYMENT');
EXIT;
END`


I am expecting Value of X.....
Some times its comming some times its not comming....
ID password values are geting passed coreectly