Oracle Reporting

For Oracle Reporting I suggest that you create a procedure for sending emails and then create a job for executing that procedure.

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')||'.


CDRS IN DB:
'||'
'; --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||' '||''||' '||''||' '||''||' '||' '; vMesg := vHead||vMesg||'
MONTHNR CALLSNR CALLS WITH DURATION
'||lc_datum||'
'||ln_st_all||'
'||ln_st_duration||'
'; 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" '); send_header('To', '"Recipient" '); 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;