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

      
MONTH
NR CALLS
NR 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;