Oracle Package SendMail

来源：互联网发布：ae cc 2015 mac注册机编辑：程序博客网时间：2024/05/22 17:41

===========================
create or replace package ygzhou.mailit as
    type addresslist_tab is table of varchar2( 200 ) index by binary_integer;
procedure p_mailusers(to_list in varchar2,
                         cc_list in varchar2,
                         subj in varchar2,
                         body in varchar2
                        );
procedure p_mailgroups(to_list in varchar2,
                         cc_list in varchar2,
                         subj in varchar2,
                         body in varchar2 );
end;
/

===================
create or replace package body ygzhou.mailit as
procedure p_mailusers( to_list in varchar2,
                         cc_list in varchar2,
                         subj in varchar2,
                         body in varchar2
                        )
               is
       v_sender     varchar2(30):='ygzhou518@sae.com.hk';
       v_mailserver varchar2(20):='10.10.1.168';
       v_port       number:=25;
       v_conn       utl_smtp.connection;
       v_crlf       varchar2(2):=chr(13)||chr(10);
       v_mesg       varchar2(4000);
       v_usrname    varchar2(30);
       v_usraddr    varchar2(100);
/*
create table ygzhou.cux_mailids(
user_alias varchar2(30 byte),
user_fname varchar2(100 byte),
user_email varchar2(100 byte),
user_group varchar2(30 byte)
);
*/
    cursor get_list ( v_tempstr in varchar2 ) is
      select user_fname, user_email
      from cux_mailids
      where user_alias like '%' || v_tempstr || '%'
        and v_tempstr is not null;
    addrlist addresslist_tab;
    addrcnt binary_integer:= 0;
begin
    if to_list is null then
      raise_application_error( -20015, 'User not entered in CUX_MAILIDS' );
    end if;
    v_conn:= utl_smtp.open_connection(v_mailserver,v_port);
    utl_smtp.helo(v_conn,v_mailserver);
    utl_smtp.mail(v_conn,v_sender);
    for listrec in get_list(to_list) loop
      utl_smtp.rcpt(v_conn,listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'To: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || v_crlf;
    end loop;
    if addrcnt = 0 then
      raise_application_error( -20016, 'No To: list generated' );
    end if;
    for listrec in get_list( cc_list ) loop
      utl_smtp.rcpt(v_conn, listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'Cc: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || v_crlf;
    end loop;
    v_mesg:= 'Date: ' || to_char( sysdate, 'dd Mon yy hh24:mi:ss' ) || v_crlf ||
           'From: Database Alert <' || v_sender || '>' || v_crlf ||
           'Subject: ' || subj || v_crlf;
    for i in 1 .. addrcnt loop
      v_mesg:= v_mesg || addrlist( i );
    end loop;
    v_mesg:= v_mesg || '' ||v_crlf || body;

    utl_smtp.data(v_conn,v_mesg );
    utl_smtp.quit(v_conn );
end;
================================================================
procedure p_mailgroups( to_list in varchar2,
                          cc_list in varchar2,
                          subj in varchar2,
                          body in varchar2 )
           is
       v_sender     varchar2(30):='ygzhou518@sae.com.hk';
       v_mailserver varchar2(20):='10.10.1.168';
       v_port       number:=25;
       v_conn       utl_smtp.connection;
       v_crlf       varchar2(2):=chr(13)||chr(10);
       v_mesg       varchar2(4000);
       v_usrname    varchar2(30);
       v_usraddr    varchar2(100);

    cursor get_list ( v_tempstr in varchar2 ) is
      select user_fname, user_email
      from cux_mailids
      where user_group like '%' || v_tempstr || '%'
        and v_tempstr is not null;
    addrlist addresslist_tab;
    addrcnt binary_integer:= 0;
begin
    if to_list is null then
      raise_application_error( -20015, 'Group not entered in CUX_MAILIDS' );
    end if;
    v_conn:= utl_smtp.open_connection(v_mailserver,v_port);
    utl_smtp.helo(v_conn,v_mailserver);
    utl_smtp.mail(v_conn,v_sender);
    for listrec in get_list( to_list ) loop
      utl_smtp.rcpt(v_conn, listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'To: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || v_crlf;
    end loop;
    if addrcnt = 0 then
      raise_application_error( -20016, 'No To: list generated' );
    end if;
    for listrec in get_list( cc_list ) loop
      utl_smtp.rcpt(v_conn,listrec.user_email );
      addrcnt:= addrcnt + 1;
      addrlist( addrcnt ):= 'Cc: ' || listrec.user_fname ||
         '<' || listrec.user_email || '>' || v_crlf;
    end loop;

    v_mesg:= 'Date: ' || to_char( systimestamp, 'dd Mon yy hh24:mi:ss TZHTZM' ) || v_crlf ||
           'From: Database Alert <' || v_sender || '>' || v_crlf ||
           'Subject: ' || subj || v_crlf;
    for i in 1 .. addrcnt loop
      v_mesg:= v_mesg || addrlist( i );
    end loop;
    v_mesg:= v_mesg || '' || v_crlf || body;

utl_smtp.data(v_conn,v_mesg );
utl_smtp.quit(v_conn );
end;

end;
/

select * from monitor.cux_metric_value where metric_id = 1
and sample_value>=10
and rownum<=5;
commit;

create or replace trigger ygzhou.t_checkaas
   after insert on ygzhou.cux_metric_value for each row
when (new.sample_value > 8 and new.metric_id = 1)
begin
mailit.p_mailgroups ('DBA','IT MANAGER',
         'Warning: '|| :new.db_name|| ' - Average Active Sessions is '|| :new.sample_value,'    Database:                  ' || :new.db_name|| chr (13)
      || '    Average Active Sessions:   '|| :new.sample_value|| chr (13)
      || '    Date:                      '|| to_char (:new.sample_time, 'yyyy-mm-dd hh24:mi:ss')|| chr (13)
   );
end;
/

0 0