#!/bin/ksh


to = "test@gmail.com"
from = "robin@gmail.com" 
subject= "mesg is mailing" 


DB_DECRYPTED_PASSWORD='xyts'; --Need advise to hide the password
DB_CONNECTION_STRING="(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=$DB_PORT))(CONNECT_DATA=(SERVICE_NAME=$DB_SID)))"
RERUN=$5
PATH=$PATH:$ORACLE_HOME/bin

echo "---------------------------------------------------"
echo  ENVIRONMENT = $ENV;
echo  DB_HOST =  $DB_HOST
echo  DB_PORT =  $DB_PORT  
echo  DB_USER =  $DB_USER
#echo  DB_PASSWORD =  $DB_PASSWORD
#echo  DB_DECRYPTED_PASSWORD =  $DB_DECRYPTED_PASSWORD
echo  DB_SID =  $DB_SID
echo  DB_CONNECTION_STRING = $DB_CONNECTION_STRING
echo "---------------------------------------------------"  


##################################################


FILE="/usr/jobs/emp_details.txt"
#FILE="emp_details.$(date +%F_%R).txt"
#touch $FILE

echo $ORACLE_HOME/bin/sqlplus
echo "======== START ============="

#$ORACLE_HOME/bin/sqlplus -s scott/tiger@ABC  <<EOF
RESULT=`$ORACLE_HOME/bin/sqlplus  -silent ${DB_USER}/${DB_DECRYPTED_PASSWORD}@${DB_CONNECTION_STRING} <<ENDA
 
 while true
  do
  15 12 * * * /usr/jobs/emp_details.txt
  30 15 * * * /usr/jobs/emp_details.txt
  sleep 900
  done
  
 set pagesize 0 feedback off verify off heading off echo off
column last_business_date format date
<SELECT QUERY> ;
exit;
ENDA`

echo "$RESULT" > $FILE
sed '/#^/G' $FILE
echo "======== END ============="

## READ AND WRITE THE FILE AND TOOK THE BACKUP
 while IFS= read -r line
 do
echo "$line" >> "emp_details.backup.$(date +%F_%R).txt"
done < "$FILE"

##this will fetch the number of lines in the file

if [ $(wc -l < "$FILE") -gt 0 ]; then

cat <<EOF; mailx -s "$subject" -r "$from"  "$to" < "/usr/jobs/emp_details.txt"

fi