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

As an aside, there is rarely any need to cd into the directory, and making the script depend on the invoking user's .bash_profile is usually not a good idea at all. And as ever, cat file | mail is better expressed mail <file. — tripleee
– tripleee, Commented Jun 11, 2015 at 7:01
You are not creating output_temp.lst and you are not capturing the output from either sqlplus or pmcmd. — tripleee
– tripleee, Commented Jun 11, 2015 at 7:21

tripleee · Accepted Answer · 2015-06-11 07:17:12Z

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).

edited Jun 11, 2015 at 7:17

answered Jun 11, 2015 at 6:59

tripleee

192k37 gold badges318 silver badges367 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nair Over a year ago

Hi Tripleee, The issue is only the SQL output is coming in the email. I want the output of the for loop also in the same file.

tripleee Over a year ago

The answer does that, and explains why your code was wrong. But the code you posted doesn't behave exactly like you describe. We can only assume that what you posted isn't exactly the code you are running, or that there are some details which you are not telling us (such as if the code is run by a cron job, which sends an email of its standard output and standard error).

Nair Over a year ago

Thanks Triplee, It worked. Earlier I haven't executed the code you have written. Appreciate your help.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related