Example : sql.shl

:
#!/bin/sh
#
#  FILE NAME...: SQL.shl
#  OBJECT NAME.: SQL
#  OBJECT TITLE: Shell Script
#  RELEASE.....: 6.0
#  PRODUCT.....: Finance
#  USAGE.......: #
#  DESCRIPTION:
#  Unix bourne shell script to execute SQL
#  Script (fzrlegi.sql) from ob Submission and handle process output
#  and messages in accordance with the Job Submission methodology.   
#
#  NOTE: Shell input parameters - $1 is the Oracle user ID
#                                 $2 is the Oracle password
#                                 $3 is the one up number
#                                 $4 is the process name
#                                 $5 is the printer name
#

#
# Process parameters passed from the Job Submission shell script (jajobs.shl).
#
UI=”$1″ # Oracle user ID
PW=”$2″ # Oracle password
ONE_UP=”$3″ # One up sequence number
PROC=”$4″ # Uppercase process
PRNT=”$5″ # Printer (only for INB)

#
# Construct SQL*Plus commands and process files names.
#
L_UI=`echo ${UI} | tr “[A-Z]” “[a-z]”` # Lowercase Oracle user ID
U_UI=`echo ${UI} | tr “[a-z]” “[A-Z]”` # Uppercase Oracle user ID
L_PW=`echo ${PW} | tr “[A-Z]” “[a-z]”` # Lowercase Oracle password
L_DB=`echo $ORACLE_SID | tr “[A-Z]” “[a-z]”` # Lowercase Oracle database instance
L_PN=`echo ${PROC} | tr “[A-Z]” “[a-z]”` # Lowercase process name

UIPW=${L_UI}/${L_PW} # Oracle user ID / Oracle password

ORACLE_CSTR=${UIPW}@${L_DB} # Oracle connect string

IN_FILE=$HOME/${L_PN}_${ONE_UP}.in # Oracle connect input control file

CTL_FILE=$U_LINKS/${L_PN}_sqlldr.ctl # SQL Loader control file
PAR_FILE=$HOME/${L_PN}_${ONE_UP}.par # SQL Loader parameter file
LDR_FILE=$HOME/${L_PN}_${ONE_UP}_sqlldr.log # SQL Loader log file
BAD_FILE=$HOME/${L_PN}_${ONE_UP}_sqlldr.bad # SQL Loader bad file
DIS_FILE=$HOME/${L_PN}_${ONE_UP}_sqlldr.dis # SQL Loader discard file

LIS_FILE=$HOME/${L_PN}_${ONE_UP}.lis # Process list file
LOG_FILE=$HOME/${L_PN}_${ONE_UP}.log # Process log file

#
# Direct an error message to the process log file if any of the parameters are missing.
#
if [ “${UI}” = “” -o “${PW}” = “” -o “${ONE_UP}” = “” -o “${PROC}” = “” ]
then
echo “Error: Process Parameter(s) Missing.” 1>${LOG_FILE} 2>&1
exit 1
fi

#
# Construct the PL/SQL script start command to run the Bummer Object User Authorization
# Process Run Parameter SQL script and write the Oracle connect information and SQL
# script start command to the SQL*Plus input control file and direct any messages to the
# process log file.
#
echo “Starting GZRSECR (Rel 6.0) …” >${LOG_FILE} 2>&1

RUN_CMND=”@gzrsecr.sql ${PROC} ${U_UI}” # SQL*Plus PL/SQL script start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Determine if authorization has been granted to run the Legacy Interface to Banner
# Process by running the Banner Object User Authorization SQL script.
#
USER_AUTH_FLAG=`sqlplus -S < ${IN_FILE}`

#
# Remove the Oracle connect input control file and direct any messages to the process
# log file.
#
rm ${IN_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# User Authorization Failure – Direct an error message to the process list file that
# the Bummer authorization process returned an error.
#
if ! [ “${USER_AUTH_FLAG}” = “YES” -o “${USER_AUTH_FLAG}” = “NO” ]
then
echo “User Authorized Failure – Unable to Run Process: ${PROC}.” >>${LOG_FILE} 2>&1
fi

#
# User not authorized to run process – Direct an error message to the process list file
# that the user is not authorized to run this process.
#
if [ “${USER_AUTH_FLAG}” = “NO” ]
then
echo “User Authorizion Status – User not Authorized to Run Process: ${PROC}.” >>${LOG_FILE} 2>&1
fi

#
# User authorized to run Process – Continue this process.
#
if [ “${USER_AUTH_FLAG}” = “YES” ]
then
echo “Username: ” ${U_UI} ” Connected.” >>${LOG_FILE} 2>&1
echo ” ” >>${LOG_FILE} 2>&1

#
# Construct the PL/SQL script start command to run the Get Job Submission Process Run
# Parameter SQL script and write the Oracle connect information and SQL script start
# command to the SQL*Plus input control file and direct any messages to the process
# log file. This execution of the Get Job Submission Process Run Parameters SQL
# script returns the location of the input data (.dat) file.
#
echo “Starting GZRGRUN (Rel 6.0) …” >> ${LOG_FILE} 2>>${LOG_FILE}

RUN_CMND=”@gzrgrun.sql ${PROC} ${ONE_UP} 01″ # SQL*Plus PL/SQL script start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Construct the SQL Loader data path parameter value by running the Get Job Submission
# Process Run Parameter SQL script.
#
DAT_PATH=`sqlplus -S < ${IN_FILE}`

#
# Remove the Oracle connect input control file and direct any messages to the process
# log file.
#
rm ${IN_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# Construct the PL/SQL script start command to run the Get Job Submission Process Run
# Parameter SQL script and write the Oracle connect information and SQL script start
# command to the SQL*Plus input control file and direct any messages to the process
# log file. This execution of the Get Job Submission Process Run Parameters SQL
# script returns the name of the input data (.dat) file.
#
echo “Starting GZRGRUN (Rel 6.0) …” >> ${LOG_FILE} 2>>${LOG_FILE}

RUN_CMND=”@gzrgrun.sql ${PROC} ${ONE_UP} 02″ # SQL*Plus PL/SQL script start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Construct the SQL Loader data file parameter value by running the Get Data File Job
# Submission Process Run Parameter SQL script.
#
DAT_FILE=`sqlplus -S < ${IN_FILE}`

#
# Remove the Oracle connect input control file and direct any messages to the process
# log file.
#
rm ${IN_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# Write the SQL Loader parameters to the SQL Loader paramter file and direct any
# messages to the process log file.
#
echo “Starting SQL*Loader …” >> ${LOG_FILE} 2>>${LOG_FILE}

echo userid=${ORACLE_CSTR} >${PAR_FILE} 2>>${LOG_FILE}
echo control=${CTL_FILE} >>${PAR_FILE} 2>>${LOG_FILE}
echo log=${LDR_FILE} >>${PAR_FILE} 2>>${LOG_FILE}
echo bad=${BAD_FILE} >>${PAR_FILE} 2>>${LOG_FILE}
echo discard=${DIS_FILE} >>${PAR_FILE} 2>>${LOG_FILE}
echo data=${DAT_PATH}${DAT_FILE} >>${PAR_FILE} 2>>${LOG_FILE}

#
# Execute SQL*Plus using the SQL*Plus input control file and direct process
# output to the process list file and messages to the process log file.
#
sqlldr PARFILE=${PAR_FILE}

#
# Remove the SQL Loader parameter file and direct any messages to the process log file.
#
rm ${PAR_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# Construct the PL/SQL script start command to run the Get Job Submission Process Run
# Parameter SQL script and write the Oracle connect information and SQL script start
# command to the SQL*Plus input control file and direct any messages to the process
# log file. This execution of the Get Job Submission Process Run Parameters SQL
# script returns the chart of accounts to be used during processing.
#
echo “Starting GZRGRUN (Rel 6.0) …” >> ${LOG_FILE} 2>>${LOG_FILE}

RUN_CMND=”@gzrgrun.sql ${PROC} ${ONE_UP} 03″ # SQL*Plus PL/SQL script start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Construct the chart of accounts parameter value by running the Get Job Submission
# Process Run Parameter SQL script.
#
COAS_CODE=`sqlplus -S < ${IN_FILE}`

#
# Remove the Oracle connect input control file and direct any messages to the process
# log file.
#
rm ${IN_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# Construct the PL/SQL script start command to run the Get Job Submission Process Run
# Parameter SQL script and write the Oracle connect information and SQL script start
# command to the SQL*Plus input control file and direct any messages to the process
# log file. This execution of the Get Job Submission Process Run Parameters SQL
# script returns the run mode to be used during processing.
#
echo “Starting GZRGRUN (Rel 6.0) …” >> ${LOG_FILE} 2>>${LOG_FILE}

RUN_CMND=”@gzrgrun.sql ${PROC} ${ONE_UP} 04″ # SQL*Plus PL/SQL script start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Construct the run mode parameter value by running the Get Job Submission Process Run
# Parameter SQL script.
#
RUN_MODE=`sqlplus -S < ${IN_FILE}`

#
# Remove the Oracle connect input control file and direct any messages to the process
# log file.
#
rm ${IN_FILE} >> ${LOG_FILE} 2>>${LOG_FILE}

#
# Construct the PL/SQL script start command to run the UNM Legacy Interface to Banner
# SQL script and write the SQL*Plus connect information and SQL script start
# command to the SQL*Plus input control file and direct any messages to the process
# log file.
#
echo “Starting ${PROC} (Rel 6.0) …” >> ${LOG_FILE} 2>>${LOG_FILE}

RUN_CMND=”@${L_PN}.sql ${DAT_PATH}${DAT_FILE} ${COAS_CODE} ${RUN_MODE}” # SQL*Plus
# PL/SQL script
# start command

echo ${ORACLE_CSTR} >${IN_FILE} 2>>${LOG_FILE}
echo ${RUN_CMND} >>${IN_FILE} 2>>${LOG_FILE}

#
# Execute SQL*Plus using the SQL*Plus input control file and direct process
# output to the process list file and messages to the process log file.
#
sqlplus -S < ${IN_FILE} > ${LIS_FILE} 2>>${LOG_FILE}

ERR_FOUND=`grep -c ERROR ${LIS_FILE}`
REJ_FOUND=`grep -c Rejected ${LIS_FILE}`
AUDIT_RUN=`grep -c Audit ${LIS_FILE}`

if [ “${ERR_FOUND}” = “0” -a “${REJ_FOUND}” = “0” ]
then
echo “Run Sequence Number: ${L_PN} completed successfully” >>${LOG_FILE} 2>&1

if [ “${AUDIT_RUN}” = “0” ]
then
USE_PATH=${DAT_PATH}”processed/”
USE_FILE=${DAT_FILE}”.processed”

mv ${DAT_PATH}${DAT_FILE} ${USE_PATH}${USE_FILE}
fi
fi

echo `wc -l < ${LIS_FILE}` “lines written to ” ${LIS_FILE} >>${LOG_FILE} 2>&1
echo ” ” >>${LOG_FILE} 2>&1

echo “Username: ” ${U_UI} ” Disconnected.” >>${LOG_FILE} 2>&1
echo ” ” >>${LOG_FILE} 2>&1
fi

#
# Save the contents of the process list file, the process log file, the SQL Loader
# log file, and the SQL Loader bad data file within the Banner database tables:
# GUBOUTP and GUROUTP if the destination is the database and direct any messages
# to the process log file.
#
if [ “${PRNT}” = “DATABASE” ]
then
echo “Starting GURINSO (Rel 3.1) …” >> ${LOG_FILE} 2>>${LOG_FILE}

if [ “${USER_AUTH_FLAG}” = “YES” ]
then
gurinso -n ${ONE_UP} -l ${LIS_FILE} -j ${PROC} -w ${UI} ${UIPW} 1>>${LOG_FILE} 2>&1
gurinso -n ${ONE_UP} -l ${LDR_FILE} -j ${PROC} -w ${UI} ${UIPW} 1>>${LOG_FILE} 2>&1
gurinso -n ${ONE_UP} -l ${BAD_FILE} -j ${PROC} -w ${UI} ${UIPW} 1>>${LOG_FILE} 2>&1
gurinso -n ${ONE_UP} -l ${DIS_FILE} -j ${PROC} -w ${UI} ${UIPW} 1>>${LOG_FILE} 2>&1
fi

gurinso -n ${ONE_UP} -l ${LOG_FILE} -j ${PROC} -w ${UI} ${UIPW} 1>>${LOG_FILE} 2>&1
fi