Sunday, February 5, 2012

Send mail in HTML format from Oracle Alert manager

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

40 comments:

  1. Good job. There is another way also to actually setup the concurrent to use printer thats driver is a shell script that does this.

    ReplyDelete
  2. Hi, We are on 12.1.3 and dont use modplsql, how do I derive the apps password dynamically. Please help

    ReplyDelete
    Replies
    1. You can't. The only way is to create procedure that will build the url with apps password automatically.

      Delete
  3. Hi Arty,
    I 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??

    ReplyDelete
    Replies
    1. Hi Raghunandan,
      If 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)

      Delete
    2. My requirement is to create only Alert. Here script means unix script or pl/sql script??
      If you have any examples can you please share..

      Delete
    3. You can you the code from send_mail.sh script(this post)
      and change the $SQL; with your sql statement

      Delete
    4. currently I'm using the script like below..
      SUBJ="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??

      Delete
    5. This is 1 select statement or 4? can you share it?

      Delete
    6. it's only one select and col1, col2, col3 are the columns which I want to send in the email.

      Delete
    7. try to add you select to the script and run it (change the required export parameters)

      Delete
    8. I have a select like this..
      select 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..

      Delete
    9. Have sent you an email to your gmail

      Delete
    10. Thank you.. I'll try and let you know the result...

      Delete
    11. Is there any limit when i use my original query am not able to paste the whole script..

      Delete
    12. Hi,

      You simply need to set your action at summary level and then put your records in between summary templates.

      -Amol

      Delete
    13. if you are getting 10 emails you need to change your message type from 'details' to 'summary'

      Delete
  4. When 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...

    ReplyDelete
  5. select
    a.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;

    ReplyDelete
  6. Where is send_mail.sh file. How to create it? i am using 12.1.3

    ReplyDelete
    Replies
    1. There is no such file available.

      /bin/sh: /bin/send_mail.sh: not found

      Delete
    2. 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

      Delete
    3. This comment has been removed by the author.

      Delete
    4. What are you want to create? scripts or custom top.
      you can use all top that you already have (for example FND_TOP). just your application top need to be FND in Operation script screen

      Delete
    5. 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.



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

      Delete
    6. 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

      Delete
    7. I 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?

      Delete
    8. you 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

      Delete
    9. This comment has been removed by the author.

      Delete
  7. Where is the send_mail.sh file. How to create it? i am using 12.1.3

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. I think you have to create the file and paste the code of the step 7

      Delete
  8. This comment has been removed by the author.

    ReplyDelete
  9. Hi,
    good 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,

    ReplyDelete
  10. I am getting error /bin/sh: ../bin/RUN_SCRIPT.sh: not found.
    APP-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)

    ReplyDelete
  11. 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.

    ReplyDelete
  12. 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.

    ReplyDelete