Hi All
My customer asked me if there is any way to send the mail from Oracle Alert Manager in HTML format.
I checked a lot of documentation and blogs but still not found the way to do it. As I understand, only plain text can be sent.
But Oracle Alert Manager can not only send message, but also run Linux script, sql statement and concurrent request.
Here the workaround to send HTML mail with Linux Script
1. Go to Alert Manager --> Define
Create a new Alert (Ex. SEND_HTML_MAIL). In Select Statement write the following test sql:
select '"select * from dba_objects where status = ''INVALID''"' into &OUTPUT from dual
In the end of procedure you will receive the mail with results for statement:
"select * from dba_objects where status = 'INVALID' "
(Pay attention: The sql statement need to be with " )
2. Actions
Create New Action as Summary --> Go to Action Details
3. Choose
Action Type: Operating System Script
Application: Your Custom Top
File: send_mail.sh &OUTPUT <'YOUR MAIL'>
where :
&OUTPUT - you sql statement
YOUR MAIL - mail address (may be more than one )
Place the send_mail.sh file in $YOUR_CUSTOM_TOP/bin directory
4. Go to Action Sets.
5. Go to Action Details.
6. Save your Alert.
7. send_mail.sh:
export SQL=$1 #Sql Statement to send
export EMAIL_LIST=$2 # To Mail List
export CC_EMAIL_LIST=<cc mail> # Cc Mail List
export BCC_EMAIL_LIST=<bcc mail> # Bcc Mail List
export LOG_FILE="/tmp/htmlwithsqlplus.log";
export SUBJECT="HTML With SQLPlus";
export SENDMAIL="/usr/sbin/sendmail";
#If you are in 11i OA version, you can get your apps password from wdbsvr.app
export APPS_PWD=`cat $IAS_ORACLE_HOME/Apache/modplsql/cfg/wdbsvr.app |grep -i -B1 apps |grep password |awk '{print $3 }'`
#You really don.t need to edit anything past the variables, unless you are familiar with HTML and sqlplus formatting options. Having a little bit of HTML knowledge could be using in making the emails more appealing.
#The following creates the email header:
# create header
echo "To: ${EMAIL_LIST}" > ${LOG_FILE};
echo "Cc: ${CC_EMAIL_LIST}" >> ${LOG_FILE};
echo "Bcc: ${BCC_EMAIL_LIST}" >> ${LOG_FILE};
echo "Subject: ${SUBJECT}" >> ${LOG_FILE};
echo "Content-Type: text/html; charset=\"us-ascii\"" >> ${LOG_FILE};
echo "" >> ${LOG_FILE};
#The following creates the start of the HTML content:
# message Starts
echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">" >> ${LOG_FILE};
echo "<html>" >> ${LOG_FILE};
echo "<head>" >> ${LOG_FILE};
echo "<title></title>" >> ${LOG_FILE};
echo "</head>" >> ${LOG_FILE};
echo "<body bgcolor=\"#ffffff\" text=\"#000000\">" >> ${LOG_FILE};
#Now it's time for the output of sqlplus:
# message body
#If you are in 11i OA version the set markup html on not set.
#So you need to use the IAS_ORACLE_HOME sqlplus version.
export ORACLE_HOME=$IAS_ORACLE_HOME
$IAS_ORACLE_HOME/bin/sqlplus -s apps/$APPS_PWD >> ${LOG_FILE} <<EOF
set markup html on
set feedback off
set PAGES 100
$SQL;
EOF
# finish message
echo "</body>" >> ${LOG_FILE};
echo "</html>" >> ${LOG_FILE};
#The following sends the message:
# mail the logfile results
${SENDMAIL} -t < ${LOG_FILE};
Good Luck ...
Another way to do it (provided by programmer Guy Lior) it's to run the following sql command in pl/sql developer / sqlplus / toad : 
