RE: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 4 Oct 2012 21:03:55 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515EC2DF4_at_NADCWPMSGCMS10.hca.corpad.net>

Right, but I'm curious about doing it in the database :) I've got it working well minus the few seconds it takes to generate the clob and attach it.

Chris

From: Vamshi Damidi [mailto:dbaprimatics_at_gmail.com] Sent: Thursday, October 04, 2012 7:27 PM To: Taylor Christopher - Nashville
Cc: oracle-l_at_freelists.org
Subject: Re: looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

Hi Chris,

Probably shell script should work fine along with mutt for attachments as email client since awr report can be spooled to html file.

Thanks,
Vamshi .D
On Thu, Oct 4, 2012 at 4:04 PM, <Christopher.Taylor2_at_parallon.net<mailto:Christopher.Taylor2_at_parallon.net>> wrote: I figured out how to send attachments using UTL_SMTP (from code found on the web) and the code I'm using uses a clob variable to build the attachment. So I pieced together a function to build a clob from DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML and call that function from my mail procedure.

This works surprisingly well to email me a daily AWR report (for a specific window of time - nightly batch cycle) *yet* the performance is slow to build the clob.

I had looked for a way to read the AWR_REPORT_HTML results directly into an attachment but I couldn't figure it out (being very non-proficient in pl/sql) so I was wondering if there was a better way to do this? (To capture the results of AWR_REPORT_HTML and mail them as an attachment).

I was thinking I should probably use a bulk collect here perhaps? Or am I overthinking this?

Here's my clob function (just for proof of concept):

CREATE OR REPLACE FUNCTION html_clob

   RETURN CLOB
IS

   p_attach_clob CLOB := NULL;

   CURSOR c1
   IS

      SELECT
             output
      FROM
             TABLE (sys.DBMS_WORKLOAD_REPOSITORY.awr_report_html (99999999,
                                                                  1,
                                                                  20003,
                                                                  20011));

BEGIN
   FOR rec IN c1
   LOOP
      p_attach_clob := p_attach_clob ||chr(13)||chr(10)|| rec.output;    END LOOP;    RETURN p_attach_clob;
END;
/

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 05 2012 - 04:03:55 CEST