Output of SQL query and command to a file in shell script

Question

I have the following shell script which runs a SQL query and a command which sends the output as an email. The issue is I am able to send only the SQL output. Not the output of the for loop. I tried to give the "EOF" after the for loop but then it gives a syntax error. Please let me know how to send both the output in an email.

Thanks & Regards, Akhil

#!/bin/bash
source $HOME/.bash_profile

cd /home/cron

wfVAR="red blue green"


echo " " > /home/cron/output.lst
sqlplus -s user/test@DB <<EOF
set linesize 55 pages 500
spool output_temp.lst;
set head off;
select sysdate from dual;
set head on;
spool off;
EOF

for name in ${wfVAR}; do
  pmcmd getworkflowdetails -sv REPOSITORY ${name} | grep -e "Workflow:" -e "Workflow run status:" -e "End time:"
 done

sed -e 's/ *$//' output_temp.lst > output.lst
cat /home/cron/output.lst | mail -s "Output - `date '+%d-%m-%y'`" [email protected]
rm output_temp.lst

Answer 1

You are overwriting the file.

echo moo >output.lst
echo bar >output.lst

Now, output.lst only contains bar.

The trick is to append, instead of overwrite.

echo moo >output.lst
echo bar >> output.lst

Your script has multiple additional problems, so it is not really clear what you actually want it to do. I'm guessing something like this.

sql things >output.lst
for var in things; do stuff; done | sed 's/ *$//' >>output.lst

... which coincidentally could also be done with a single redirection if you wanted to, by running the commands in a subshell, and redirecting output from the subshell:

( sql things
  for var in things; do stuff; done | sed 's/ *$//' ) >output.lst

With these speculations, your entire script could be

#!/bin/bash
# source $HOME/.bash_profile   ######## XXX probably don't do this

output=/home/cron/output.lst

sqlplus -s user/test@DB <<EOF >$output   # Capture output from SQL
set linesize 55 pages 500
spool output_temp.lst;
set head off;
select sysdate from dual;
set head on;
spool off;
EOF

for name in red blue green; do
  pmcmd getworkflowdetails -sv REPOSITORY "$name"
done |
grep -e "Workflow:" -e "Workflow run status:" -e "End time:" |
# XXX: could benefit from refactoring grep x | sed y to just sed '/x/!d;y'
sed -e 's/ *$//' >> $output
# XXX: fixed Useless Use of cat Award
mail -s "Output - `date '+%d-%m-%y'`" [email protected] <$output

Even the permanent output file could be avoided if you like; just pipe to mail.

#!/bin/bash

( sqlplus -s user/test@DB <<__EOF
    set linesize 55 pages 500
    spool output_temp.lst;
    set head off;
    select sysdate from dual;
    set head on;
    spool off;
__EOF

  for name in red blue green; do
    pmcmd getworkflowdetails -sv REPOSITORY "$name"
  done |
  sed -e '/Workflow:\|Workflow run status:\|End time:/!d' -e 's/ *$//' ) |

mail -s "Output - `date '+%d-%m-%y'`" [email protected]

(... assuming your sed understands \| to mean alternation in a regex).