Shell Programming and Scripting

SQL> select * from location_queue
LOCATION_CODE                   TIMING      ID
------------------------------ ---------    -----
DETAILS.LNM CHENNAI,2000           20-JUL-12 KMK
DETAILS.LNM BANGLORE,6000          18-JUL-12 KMS
DETAILS.LNM SALEM,5000             18-JUL-12 KMS
DETAILS.LNM TIRUPUR,2000           18-JUL-12 KMS
DETAILS.LNM TRICHY,2000            18-JUL-12 KMS
DETAILS.SKM                        28-NOV-12 PRM 
DETIALS.SKM                        28-NOV-12 PRM 
DETAILS.MKDM                       01-DEC-12 PRM

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"

 
cat location.txt
chennai
banglore
salem
 tirchy
tirupur