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 " )
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 ...
Good job. There is another way also to actually setup the concurrent to use printer thats driver is a shell script that does this.
ReplyDeleteHi, We are on 12.1.3 and dont use modplsql, how do I derive the apps password dynamically. Please help
ReplyDeleteYou can't. The only way is to create procedure that will build the url with apps password automatically.
DeleteHi Arty,
ReplyDeleteI have one periodic alert. Which will send an alert daily at a certain time. My SQL is returning 10 rows. so I'm getting 10 emails.
My action type is Operating System Script. I cannot use Message Action type. Is there any way we can send all the returned rows in a single email using Operating System Script??
Hi Raghunandan,
DeleteIf you have only one alert to send from your EBS, I advice you to create a little script and just schedule it with the crontab (scheduled concurrent can be also good)
My requirement is to create only Alert. Here script means unix script or pl/sql script??
DeleteIf you have any examples can you please share..
You can you the code from send_mail.sh script(this post)
Deleteand change the $SQL; with your sql statement
currently I'm using the script like below..
DeleteSUBJ="ABC"
TO=xyz@xyz.com
(
cat << !
To : ${TO}
Subject : ${SUBJ}
!
cat << !
SQL COL1: &data1
SQL COL2: &data2
SQL COL3: &data3
SQL COL4: &data4
!
) | sendmail -v ${TO}
can i replace this with the above??
This is 1 select statement or 4? can you share it?
Deleteit's only one select and col1, col2, col3 are the columns which I want to send in the email.
Deletetry to add you select to the script and run it (change the required export parameters)
DeleteI have a select like this..
Deleteselect owner, object_name, object_type, created, last_ddl_time from all_objects;
I'm not that much familiar with unix scripting.. can you please help me with the above query..
how can i replace the parameter n all..
Which EBS version you use?
DeleteR12.2.4
DeleteHave sent you an email to your gmail
DeleteThank you.. I'll try and let you know the result...
DeleteIs there any limit when i use my original query am not able to paste the whole script..
DeleteHi,
DeleteYou simply need to set your action at summary level and then put your records in between summary templates.
-Amol
if you are getting 10 emails you need to change your message type from 'details' to 'summary'
DeleteWhen i write my actual query in the $SQL part am not able to paste the whole script in the text of operating system script action type.. the alr_action table's body column only take 2000chars...
ReplyDeleteselect
ReplyDeletea.tablespace_name,
round(SUM(a.bytes)/(1024*1024*1024)) CURRENT_GB,
round(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024)))) MAX_GB,
(SUM(a.bytes)/(1024*1024*1024) - round(c.Free/1024/1024/1024)) USED_GB,
round((SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))) - (SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))),2) FREE_GB,
round(100*(SUM(a.bytes)/(1024*1024*1024) -
round(c.Free/1024/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/(1024*1024*1024),
b.maxextend*8192/(1024*1024*1024))))) USED_PCT
from
dba_data_files a,
sys.filext$ b,
(SELECT
d.tablespace_name ,sum(nvl(c.bytes,0)) Free
FROM
dba_tablespaces d,
DBA_FREE_SPACE c
WHERE
d.tablespace_name = c.tablespace_name(+)
group by d.tablespace_name) c
WHERE
a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP BY a.tablespace_name, c.Free/1024
ORDER BY tablespace_name;
Where is send_mail.sh file. How to create it? i am using 12.1.3
ReplyDelete$YOUR_CUSTOM_TOP/bin
DeleteThere is no such file available.
Delete/bin/sh: /bin/send_mail.sh: not found
you need to create the send_mail.sh from step 7 and put it to your custom top directory. the custom top need be also defined in CONTEXT_FILE
DeleteThis comment has been removed by the author.
DeleteWhat are you want to create? scripts or custom top.
Deleteyou can use all top that you already have (for example FND_TOP). just your application top need to be FND in Operation script screen
I want to create send_mail.sh file and copy to custom top. When i run alert, i am getting error and below are the log.
Delete/bin/sh: /.../../bin/send_mail.sh: not found
APP-ALR-04021: Oracle Alert was unable to perform the command "/bin/send_mail.sh "select owner,object_name, object_type from dba_objects where status='INVALID'" 'email@domain.com'". Check that this is a valid command on your operating system. (ROUTINE=alssoa) (FILE=alr/lib/alspsa.c) (LINE=459)
You need to create a send_mail.sh firt. the code of the script in step 7. create it, put it to the custom top and run the alert
DeleteI don't know how to create with above code? is it required system administrator or i can create it my own? If possible, can you share created file and send to my email id?
Deleteyou need connect to your linux server with appl user, cd to custom_top/bin folder and just create a file send_mail.sh with code from step 7. send me you mail, and i will send you the file
DeleteThis comment has been removed by the author.
DeleteWhere is the send_mail.sh file. How to create it? i am using 12.1.3
ReplyDeleteThis comment has been removed by the author.
DeleteI think you have to create the file and paste the code of the step 7
DeleteThis comment has been removed by the author.
ReplyDeleteHi,
ReplyDeletegood job. I would like to use it but I am getting this in my log:
/u01/oracle/DES......../bin/send_mail.sh: line 5:
: command not found
/u01/oracle/DES......../bin/send_mail.sh: line 6:
: command not found
/u01/oracle/DES......../bin/send_mail.sh: line 7:
: command not found
Please Do you have any idea about this error,
Thanks in advance,
Regards,
I am getting error /bin/sh: ../bin/RUN_SCRIPT.sh: not found.
ReplyDeleteAPP-ALR-04021: Oracle Alert was unable to perform the command "../bin/RUN_SCRIPT.sh <email?". Check that this is a valid command on your operating system. (ROUTINE=alssoa) (FILE=alr/lib/alspsa.c) (LINE=458)
I'm Абрам Александр a businessman who was able to revive his dying lumbering business through the help of a God sent lender known as Benjamin Lee the Loan Consultant. Am resident at Yekaterinburg Екатеринбург. Well are you trying to start a business, settle your debt, expand your existing one, need money to purchase supplies. Have you been having problem trying to secure a Good Credit Facility, I want you to know that Mr Benjamin will see you through. Is the right place for you to resolve all your financial problem because am a living testimony and i can't just keep this to myself when others are looking for a way to be financially lifted.. I want you all to contact this God sent lender using the details as stated in other to be a partaker of this great opportunity Email: 247officedept@gmail.com Or WhatsApp/Text +1-989-394-3740.
ReplyDeleteI'm Абрам Александр a businessman who was able to revive his dying lumbering business through the help of a God sent lender known as Benjamin Lee the Loan Consultant. Am resident at Yekaterinburg Екатеринбург. Well are you trying to start a business, settle your debt, expand your existing one, need money to purchase supplies. Have you been having problem trying to secure a Good Credit Facility, I want you to know that Mr Benjamin will see you through. Is the right place for you to resolve all your financial problem because am a living testimony and i can't just keep this to myself when others are looking for a way to be financially lifted.. I want you all to contact this God sent lender using the details as stated in other to be a partaker of this great opportunity Email: 247officedept@gmail.com Or WhatsApp/Text +1-989-394-3740.
ReplyDelete