===========================
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;
/