looking for thoughts/advice on AWR_REPORT_HTML and UTL_SMTP
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Oct 2012 15:04:19 -0500
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;
/
--
//www.freelists.org/webpage/oracle-l
Other related posts:
