DECLARE
bid NUMBER;
eid NUMBER;
host_name VARCHAR2(64);
starttime CHAR (5);
endtime CHAR (5);
v_from VARCHAR2 (80);
v_recipient VARCHAR2 (80) := 'gokhan@myemailaddress.com';
v_mail_host VARCHAR2 (30) := 'oursmtpserver';
v_mail_conn UTL_SMTP.connection;
BEGIN
starttime := '06:00';
endtime := '10:00';
SELECT MIN (snap_id), MAX (snap_id)
INTO bid, eid
FROM dba_hist_snapshot
WHERE TO_CHAR (begin_interval_time, 'hh24:mi') >= starttime
AND TO_CHAR (end_interval_time, 'hh24:mi') <= endtime
AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
AND TRUNC (end_interval_time) = TRUNC (SYSDATE);
FOR rac IN (SELECT dbid, inst_id, db_unique_name
FROM gv$database)
LOOP
SELECT host_name
INTO host_name
FROM gv$instance where inst_id = rac.inst_id;
v_from := rac.db_unique_name || '@' || host_name;
v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
UTL_SMTP.HELO (v_mail_conn, v_mail_host);
UTL_SMTP.MAIL (v_mail_conn, v_from);
UTL_SMTP.RCPT (v_mail_conn, v_recipient);
UTL_SMTP.OPEN_DATA( v_mail_conn );
UTL_SMTP.WRITE_DATA ( v_mail_conn, 'From:' || v_from || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA ( v_mail_conn, 'To:' || v_recipient || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA ( v_mail_conn, 'Subject: '
|| 'AWR Report of ' || v_from || ' '
|| SYSDATE || ' ' || starttime || '-' || endtime || UTL_TCP.CRLF );
UTL_SMTP.WRITE_DATA ( v_mail_conn,
'Content-Type: text/html; charset=utf8'
|| UTL_TCP.CRLF || UTL_TCP.CRLF );
FOR c1_rec IN
(SELECT output
FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid,
rac.inst_id, bid, eid, 8 )))
LOOP
UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
END LOOP;
UTL_SMTP.CLOSE_DATA (v_mail_conn);
UTL_SMTP.QUIT (v_mail_conn);
END LOOP;
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
THEN
RAISE_APPLICATION_ERROR (-20000, 'Unable to send mail: ' || SQLERRM);
END;
/
