Script to send mail and query output through mailx function

Step 1: Make a folder save the following three files:

1. Check.sh
2. Query.sql
3. message-body.txt

Step 2: Save below in check.sh

##############################################
# oracle oratab file location
cd /u01/app/oracle/script/ROLFSCHEDULEDJOB
ORATAB=/etc/oratab
if [ ! -f $ORATAB ]
then
exit 1
fi
# Loop for every entry in ORATAB file
cat $ORATAB|grep -v '^$' | while read LINE
do        case $LINE in
\#*)    # Comment-Line in ORATAB
;;        *)
# Setup ORACLE_SID and ORACLE_HOME
# if third field in ORATAB is 'Y'
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ]
then
ORACLE_SID=""
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
#echo ORACLE_SID=$ORACLE_SID
#echo ORACLE_HOME=$ORACLE_HOME
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
#export PATH=$PATH:$ORACLE_HOME/bin
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib
exit|$ORACLE_HOME/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" / as sysdba @query.sql > $ORACLE_SID.html
esac # End case $LINE
done
# End Loop read ORATAB
( cat body-message.txt; uuencode NRESST.html NRESST.html; uuencode NRESST11.html NRESST11.html;uuencode NRESSQ.html NRESSQ.html)
| mail -s "OBIWAN Server Reports" mail@mail.com -c mail@mail.com

Step 3: Save query in query.sh folder

select * from instance;

Step 4: Save file message body:

Hi,

Please find the attached reports.

It's auto-generated email, kindly do not reply to this mail.

Regards,
Oracle DBA Team