Shell Programming and Scripting

cat location.sh

sales=1000;
location=&1

Function 1  () {
        VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        SET serveroutput on
        select LOCATION_COUNT.CHENNAI_BANG
               (&LOCATION,&SALES) from dual;
        exit;
EOF
               }
 
function2 () {
VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        select :LOCATION_COUNT.SAL_TRICHY_PONDI
               (&LOCATION,&SALES) from dual;
        exit;
EOF`
 
     }
function3 () {
               VALUE=`sqlplus -S /NOLOG << EOF
        CONNECT dbs/passwd@dbtod
        SET head off
        select LOCATION_COUNT.GET_TIRUP
               (&LOCATION,&SALES); from dual;
        exit;
EOF`
           }

(&${location},&${sales})

 
 
cat function.sh
sales=10000;
function_value_1(){
value_1_name=$1
value_1=`sqlplus -S /NOLOG << EOF
  CONNECT dbs/passwd@dbtod
  SET head off
  select put_valueS.$value_1_name
     (&location_id,&SALES) from dual;
  exit;
EOF`}
function_value_2(){
value_2=`sqlplus -S /NOLOG << EOF
  CONNECT dbs/passwd@dbtod
  SET head off
  select put_valueS.put_citywise_value(&location_id,&sales) from dual;
  exit;
EOF`}
function_value_3(){
value3=`sqlplus -S /NOLOG << EOF
  CONNECT dbs/passwd@dbtod
  SET head off
  select put_valueS.put_placewise_value(&location_id,&sales) from dual;
  exit;
EOF`}
function_record_delete () {
sqlplus -S /NOLOG << EOF
  CONNECT dbs/passwd@dbtod
  SET head off
SELECT value(*) INTO count_value FROM TOTAL_value WHERE location_id =&location_id AND sales =&sales;
if [[ "&count_value" >0 ]]
then
EXEC SQL DELETE FROM TOTAL_value WHERE location_id =&location_id AND sales=&sales; 
exit;
EOF}
 
while read location_id
do
  case $location_id in
    chennai|banglore)
      function_value_1 chennai_bang ;;
      function_value_1 put_area_loc_value ;;
    salem|trichy||kovai)
      function_record_delete ;;
      function_value_1 salem_trichy ;;
    tirupur)
      function_record_delete ;;
      function_value_1 tirupur ;;
    *)
      echo "location is out of the range $location_id" ;;
  esac
if [[ "$value_1" =0 ]]then
value2 ;;
value3 ;;
fi
if [[ "$value_1" =0 && "$value_2" =0 && "$value_3" =0]] then
echo"job failed"
else
echo "job sucess"
fi
done < "location.txt"

function_out()
{
function=$1
location_id=$2
sales=$3
echo $function
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
        SET head off
        SET serveroutput on
        spool chkbits.txt
        select PUT_UNIX.$function('$location_id',$sales) from dual;
        spool off
exit
EOF
}
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
set pagesize 0
spool queryout.txt
SELECT PROCESS_CODE FROM scheduled_process_queue where PROCESS_CODE like '%INFO.SDCNT%';
spool off
exit
EOF
while IFS='[, ]' read job location_id sales; do
count=`sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
set echo off
set heading off
set feedback off
Set pagesize 0
SET head off
spool output.txt
select count(*)  from total_count where location_id='$location_id' and sales=$sales;
spool off
exit
EOF`
count=`cat output.txt`
if [[ $count > 0 ]];
then
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
delete from total_count where location_id='$location_id' and sales=$sales;
commit;
exit
EOF
echo "count is not  gretaehr than zero"
else
echo "count is zero"
fi
case $location_id in
DRUG|GROC)
function_out PUT_GROC_DRUG_COUNT $location_id $sales;;
CLUB|MASS|DECA|LIQ|PETS)
function_out PUT_OTHER_location_COUNT $location_id $sales;;
CONV)
function_out PUT_OTHER_location_COUNTi $location_id $sales;;
*)echo "$location_id location_id is not in the database";;
esac
out=`cat chkbits.txt`
if [ $out = 0 ]
then
echo "Check TRC & lenap Function "
sqlplus -S xxxx/yyyy@xxxxdbt <<EOF
SET head off
SET serveroutput on
spool bits1.txt
select PUT_UNIX.PUT_UNIX.PUT_TRC_COUNT('$location_id',$sales) from dual;
spool off
spool bits2.txt
select PUT_UNIX.PUT_UNIX.PUT_lenap_COUNT('$location_id',$sales) from dual;
spool off
exit
EOF
else
echo "failire"
fi
if [[ $out = 0 && $bits1 = 0 && $bits2 = 0 ]]
then
mailx -s "Alert Email for KMDS JOBS Suceess for location_id $location_id $sales" arunkumar@gmail.com  < /dev/null
else
mailx -s "Alert Email for KMDS JOBS Failed for location_id $location_id $sales"  arunkumar@gmail.com < /dev/null
fi
rm output.txt
rm bits1.txt
rm bits2.txt
rm chkbits.txt
done < out.txt
rm out.txt