For Oracle Reporting I suggest that you create a procedure for sending emails and then create a job for executing that procedure.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
CREATE OR REPLACE PROCEDURE ORACLE_REPORTING IS c utl_smtp.connection; vHead VARCHAR2(32767); vMesg VARCHAR2(32767); lc_sql VARCHAR2(32767); lc_datum VARCHAR2(20); ln_st_all NUMBER(10); ln_st_duration NUMBER(10); PROCEDURE send_header(name VARCHAR2, header VARCHAR2) AS BEGIN IF name is null AND header is not null THEN utl_smtp.write_data(c, header || UTL_TCP.CRLF); ELSE utl_smtp.write_data(c,name ||':'|| header || UTL_TCP.CRLF); END IF; END; BEGIN vHead := 'NUMBER EVENTS PER MONTH '||to_char(add_months(sysdate,-1), 'mm.yyyy')||'. <br /><br /> CDRS IN DB: <br />'||' <br /> <table border="1"> <tr><th>MONTH</th><th>NR CALLS</th><th>NR CALLS WITH DURATION</th></tr> '; --get month and year select to_char(add_months(sysdate,-1), 'mm.yyyy') into lc_datum from dual; --all calls lc_sql := ' select nvl(count(*), 0) st '|| ' from ( select * '|| ' from oracle_table partition(REC_'||to_char(add_months(sysdate,-1), 'yyyymm')||'A)'|| ' where received_called_number = ''1808'' '|| ' union all'|| ' select * '|| ' from oracle_table partition(REC_'||to_char(add_months(sysdate,-1), 'yyyymm')||'B)'|| ' where received_called_number = ''1808'' '|| ' )'; -- dbms_output.put_line(lc_sql); EXECUTE IMMEDIATE lc_sql into ln_st_all; --calls with duration lc_sql := ' select nvl(count(*), 0) st '|| ' from ( select * '|| ' from mbi_cdr partition(MBI_REC_'||to_char(add_months(sysdate,-1), 'yyyymm')||'A)'|| ' where (received_called_number = ''1808'' '|| ' and to_number(elapsed_time) > 0 '|| ' union all'|| ' select * '|| ' from mbi_cdr partition(MBI_REC_'||to_char(add_months(sysdate,-1), 'yyyymm')||'B)'|| ' where (received_called_number = ''1808'' '|| ' and to_number(elapsed_time) > 0 '|| ' )'; -- dbms_output.put_line(lc_sql); EXECUTE IMMEDIATE lc_sql into ln_st_duration; vMesg := vMesg||' '||'<tr><td>'||lc_datum||'</td>'||' '||'<td><center>'||ln_st_all||'</center></td>'||' '||'<td><center>'||ln_st_duration||'</center></td>'||' </tr>'||' '; vMesg := vHead||vMesg||'</table>'; c := utl_smtp.open_connection('11.22.20.11'); utl_smtp.mail(c, 'company@sql-tuning.com'); utl_smtp.rcpt(c, 'a@sql-tuning.com'); utl_smtp.rcpt(c, 'b@sql-tuning.com'); utl_smtp.open_data(c); send_header('', 'Content-type: text/html; charset=windows-1250'); send_header('From', '"Oracle Reporter" <company@sql-tuning.com>'); send_header('To', '"Recipient" <a@sql-tuning.com>'); send_header('Subject', 'ORACLE REPORTING STATISTICS '||to_char(add_months(sysdate,-1), 'mm.yyyy')); utl_smtp.write_data(c, UTL_TCP.CRLF || vMesg ); utl_smtp.close_data(c); utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN BEGIN utl_smtp.quit(c); EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN NULL; END; raise_application_error(-20000, SQLERRM); END; |