looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP

From: <Christopher.Taylor2_at_parallon.net>
Date: Thu, 4 Oct 2012 15:04:19 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E88515EC2AF9_at_NADCWPMSGCMS10.hca.corpad.net>



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
Received on Thu Oct 04 2012 - 22:04:19 CEST

Original text of this message