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.
FOR rec IN c1
LOOP
p_attach_clob := p_attach_clob ||chr(13)||chr(10)|| rec.output; END LOOP; RETURN p_attach_clob;
END;
/
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-lReceived on Thu Oct 04 2012 - 22:04:19 CEST