Problem in sending mail with database content

Shell Programming and Scripting
1

Hi All,
I want to fetch records from Oracle DB table and send it in a mail to a set of users. i.e, I have a query which returns a set of records. I want to send mail with below content:

Hi ,
PFB the details:
 
<first database record>
<Second database record>
�����.
������..
�������..
<n database record>

I am using SPOOL command to get result set from db table in a file result.txt and trying to append it with other text (Hi All,�����. ) and send it in mail.
Below is my code but it just creating a file result.txt with result set from oracle table.

#!/bin/sh
 
EMAIL="anil.kumar@abc.com"
EMAILMESSAGE="/app/to/path/result.txt"
SUBJECT=" testmail"
 
 
echo "Hi," >> $EMAILMESSAGE
 
echo "PFB the Details:" >> $EMAILMESSAGE
 
sqlplus -s userid@dbname/pwd<<END
 
SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET WRAP OFF ;
 
spool /app/to/path/result.txt 
 
select distinct entity_id, fund_name from fi_ss_risk_ctry_dts
where rownum<100;
 
spool off;
exit;
 
/bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
rm /app/to/path/result.txt

Where is my code misbehaving. What can be the problem? Please suggest.

Thanks in advance.

Regards,
Anil

2

try below

#!/bin/sh
EMAIL="anil.kumar@abc.com"
EMAILMESSAGE="/app/to/path/result.txt"
SUBJECT=" testmail"
echo "Hi," >> $EMAILMESSAGE

echo "PFB the Details:" >> $EMAILMESSAGE

sqlplus -s userid@dbname/pwd<<END

SET HEADING OFF ;
SET NEWPAGE NONE ;
SET FEEDBACK OFF ;
SET WRAP OFF ;
spool /app/to/path/result1.txt
select distinct entity_id, fund_name from fi_ss_risk_ctry_dts
where rownum<100;
spool off;
exit;
END
cat /app/to/path/result1.txt >> $EMAILMESSAGE

/bin/mail -s "$SUBJECT" "$EMAIL" < $EMAILMESSAGE
rm /app/to/path/result.txt
rm /app/to/path/result1.txt
3

Thanks. It worked fine.

Now, there is one more problem that the output in mail is not properly arranged. I want database content to be in tabular format with column headings and center aligned.

Any help in this regard would be highly appreciated.

Regards,
Anil

4

Try not to set headings OFF, and try to JUSTIFY columns.