Many a times there is a need to do a reverse interface
between Oracle application and legacy system. Also data extracts
from
Oracle application is need to be put in Windows
environment in text file format to be used in a spread sheet format.
This
shell script provided here does the following--
1. It takes an data extract file ( that can be
a spooled data file generated by sql script) from Oracle Application.
2. It transfers the data file to IDS database
server.
3. Archives the file and compresses it.
4. It sends the file to a LAN server.
The program is kept in the $CUSTOM_TOP/bin directory.
Note : The first four parameter $1, $2,$3, $4
are reserved for processing by Oracle Applications and the parameter passed
by the application is trapped in $5. For parameters greater than $9 you
have to use awk / print commands.
# PROGRAM NAME : FTP concurrent program
# AUTHOR : A Shrivastava
# DESCRIPTION : This Script FTPs datafile to Mainframe.
# CHANGE HISTORY:
# -----------------------------------------------------------------
# DATE RESON FOR CORRECTION
# -----------------------------------------------------------------
# 14-FEB-2001 This script ftps data
# extract. The data FTP is in ascii.
# The file is put onto two serves depending
# upon the parameters.
#
# ******************************************************************
FTP_Files() {
### Log Input Parameters assigned to Program Variables ###
echo " " > ${FTP_LOG}
echo "*********************************************" >> ${FTP_LOG}
echo "******* LOG FILE DETAILS BEGIN HERE *******" >> ${FTP_LOG}
echo "*********************************************" >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
echo "******* FTP FILE ******* " >> ${FTP_LOG}
echo "*** Input Parameters ***" >> ${FTP_LOG}
echo "${REMOTEserver}" >> ${FTP_LOG}
echo "${UNAME}" >> ${FTP_LOG}
echo "${PASS}" >> ${FTP_LOG}
echo "${COMMND}" >> ${FTP_LOG}
echo "${HOSTpath}" >> ${FTP_LOG}
echo "${REMOTEpath}" >> ${FTP_LOG}
echo "${TRANSfile}" >> ${FTP_LOG}
echo "*********** End **********" >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
### Test the existence of the Remote Server ###
ping ${REMOTEserver} >> ${FTP_LOG}
TESTval=${?}
if [ ${TESTval} -ne 0 ]
then
echo " Remote File Server Unavailable -${REMOTEserver}"
echo "Remote File Server Unavailable - ${REMOTEserver}"
echo "File Transfer Failed " >> ${FTP_LOG}
echo "Remote File Server Unavailable - ${REMOTEserver}" >> ${FTP_LOG}
Return_func 1
fi
COMMNDline="put $HOSTpath/${TRANSfile}"*" "DATAOUTPUT""
echo "${COMMNDline}" >> ${FTP_LOG}
# ### Log FTP Formated Commands ###
echo " " >> ${FTP_LOG}
echo "*** FTP Commands ***" >> ${FTP_LOG}
echo "open ${REMOTEserver}" >> ${FTP_LOG}
echo "user ${UNAME} ${PASS}" >> ${FTP_LOG}
echo "lcd .." >> ${FTP_LOG}
echo "lcd ${HOSTpath}" >> ${FTP_LOG}
echo "cd ${REMOTEpath}" >> ${FTP_LOG}
echo "ascii" >> ${FTP_LOG}
echo "quote site space\(10,10\) lrecl\(618\) blk\(27810\)" >> ${FTP_LOG}
echo "${COMMNDline}" >> ${FTP_LOG}
echo "close" >> ${FTP_LOG}
echo "quit" >> ${FTP_LOG}
echo "******** End ******* " >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
### Execute FTP Formated Commands ###
if (echo "open ${REMOTEserver}"
echo "user ${UNAME} ${PASS}"
echo "lcd ${HOSTpath}"
echo "cd .."
echo "cd ${REMOTEpath}"
echo "ascii"
echo "quote site space\(10,10\) lrecl\(618\) blk\(27810\)"
echo "${COMMNDline}"
echo "close"
echo "quit"
) | /usr/bin/ftp -inv 2>&1 \
| tee -a ${FTP_LOG} \
| grep "^226 " > /dev/null
then
echo "File Transfer Successful "
rm $HOSTpath/${TRANSfile}*
else
echo "File Transfer Failed"
echo "File Transfer Failed" >> ${FTP_LOG}
Return_func 1
fi
}
FTP_Files_To_LAN() {
### Log Input Parameters assigned to Program Variables for LAN xfer ###
echo " " > ${FTP_LOG}
echo "*********************************************" >> ${FTP_LOG}
echo "*********************************************" >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
echo "******* FTP FILE to LAN ******* " >> ${FTP_LOG}
echo "*** Input Parameters ***" >> ${FTP_LOG}
echo "${LANREMOTEserver}" >> ${FTP_LOG}
echo "${LANUNAME}" >> ${FTP_LOG}
echo "${LANPASS}" >> ${FTP_LOG}
echo "${LANCOMMND}" >> ${FTP_LOG}
echo "${LANREMOTEpath}" >> ${FTP_LOG}
echo "*********** End **********" >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
### Test the existence of the Remote Server ###
ping ${LANREMOTEserver} >> ${FTP_LOG}
LANTESTval=${?}
if [ ${LANTESTval} -ne 0 ]
then
echo " Remote LAN File Server Unavailable -${LANREMOTEserver}"
echo "Remote LAN File Server Unavailable - ${LANREMOTEserver}"
echo "File Transfer Failed " >> ${FTP_LOG}
echo "Remote LAN File Server Unavailable - ${LANREMOTEserver}" >> ${FTP_LOG}
Return_func 1
fi
LANCOMMNDline="put $HOSTpath/${TRANSfile}"*" "DATAOUTPUT""
echo "${LANCOMMNDline}" >> ${FTP_LOG}
# ### Log FTP Formated Commands ###
echo " " >> ${FTP_LOG}
echo "*** FTP Commands ***" >> ${FTP_LOG}
echo "open ${LANREMOTEserver}" >> ${FTP_LOG}
echo "user ${LANUNAME} ${LANPASS}" >> ${FTP_LOG}
echo "lcd .." >> ${FTP_LOG}
echo "lcd ${HOSTpath}" >> ${FTP_LOG}
echo "cd ${LANREMOTEpath}" >> ${FTP_LOG}
echo "ascii" >> ${FTP_LOG}
echo "${LANCOMMNDline}" >> ${FTP_LOG}
echo "close" >> ${FTP_LOG}
echo "quit" >> ${FTP_LOG}
echo "******** End ******* " >> ${FTP_LOG}
echo " " >> ${FTP_LOG}
### Execute FTP Formatted Commands for LAN###
if (echo "open ${LANREMOTEserver}"
echo "user ${LANUNAME} ${LANPASS}"
echo "lcd ${HOSTpath}"
echo "cd .."
echo "cd ${LANREMOTEpath}"
echo "ascii"
echo "${LANCOMMNDline}"
echo "close"
echo "quit"
) | /usr/bin/ftp -inv 2>&1 \
| tee -a ${FTP_LOG} \
| grep "^226 " > /dev/null
then
echo "File Transfer to LAN Successful "
else
echo "File Transfer to LAN Failed"
echo "File Transfer to LAN Failed" >> ${FTP_LOG}
Return_func 1
fi
}
Archive_File () {
cp ${1} ${1}.${FLEXT} >> ${FTP_LOG}
File="${1}.${FLEXT}"
echo $1 >> ${FTP_LOG}
echo $File >> ${FTP_LOG}
compress ${File}
if [ $? -eq "0" ]
then
ar -rvV ${ARCHIVE_FILE} ${File}.Z >> ${FTP_LOG}
if [ $? -eq "0" ]
then
rm -f ${File}.Z
echo "Data file Archived succesfully"
else
echo "Could Not Archive the file"
fi
else
ar -rvV ${ARCHIVE_FILE} ${File} >> ${FTP_LOG}
if [ $? -eq "0" ]
then
rm -f ${File}
echo "Data file Archived succesfully"
else
echo "Could Not Archive the file"
fi
fi
# List all the files archived into $ARCHIVE_FILE
echo "******* List of all the Archived Files in ${ARCHIVE_FILE} *******" >> ${FTP_LOG}
ar -tvV ${ARCHIVE_FILE} >> ${FTP_LOG}
}
Return_func() {
RTN_CODE=$1
cat ${FTP_LOG}
case ${RTN_CODE} in
0)
exit ${RTN_CODE}
;;
*)
exit ${RTN_CODE}
;;
esac
}
# Main Program
# Concurrent Program Parameters
# The $1 to $4 parameters are reserved for the concurrent program. The rest of the
# param i.e. $5 is the first defined param in the Concurrent Program params and so on.
# The username, password, servername, LAN servername all carry value in default parameters
# in the concurrent programs parameters.
FLEXT=`date +%m%d%y%H%M%S`
CUST_DIR="${NA_TOP}"
FTP_LOG="${CUST_DIR}/log/.ftp_file_atdis.log"
ARCHIVE_DIR="${CUST_DIR}/outbound/archive"
echo 'Arguments:'
echo Remote Server Name : $5
echo Username : $6
echo Password : $7
echo DSN Prefix : $8
echo Host File Path : $9
TRANSfile=`echo $* | awk '{print $10}'`
echo File Prefix : ${TRANSfile}
# Lan Input
echo "LAN INPUT"
echo LAN Remote Server Name : `echo $* | awk '{print $11}'`
echo LAN Username : `echo $* | awk '{print $12}'`
echo LAN Password : `echo $* | awk '{print $13}'`
echo LAN Remote Path : `echo $* | awk '{print $14}'`
ARCHIVE_FILE="${ARCHIVE_DIR}/PHRATDIS.ar"
DATA_FILENAME=`echo ${CUST_DIR}/outbound/${TRANSfile}*`
REMOTEserver="`echo ${5} | sed 's/\"//g'`"
UNAME="`echo ${6} | sed 's/\"//g'`"
PASS="`echo ${7} | sed 's/\"//g'`"
HOSTpath="`echo ${9} | sed 's/\"//g'`"
REMOTEpath="`echo ${8} | sed 's/\"//g'`"
COMMND="put"
echo Check log for output >> $APPLCSF/$APPLOUT/$3.$4
#Lan Params
# The variables on the command line need special treatment after $9.
#Instead of getting the value of the variable directly you need to print it with awk.
LANREMOTEserver=`echo $* | awk '{print $11}'`
LANUNAME=`echo $* | awk '{print $12}'`
LANPASS=`echo $* | awk '{print $13}'`
LANREMOTEpath=`echo $* | awk '{print $14}'`
LANCOMMND="put"
# Main Program Logic
Archive_File ${DATA_FILENAME}
FTP_Files_To_LAN
FTP_Files
Return_func 0
# EXIT
Return to : Oracle Database,
SQL, Application, Programming Tips