在oracle中利用utl_smtp和trigger结合来实现系统报警

应用经常在oracle数据库中存放一些开关量,而一些开关在一些情况下会自动修改状态,如果不能及时发现这些状态的变化可能会导致应用错误,也可能让你半夜从家中到公司救火,为了解决这个问题,我用oracle的utl_smtp和trigger结合来实现mail报警,使系统在状态发生变化的第一时间就能发现,及时解决问题.我的具体做法是:

1.在自己的管理帐号中建立一个stored procedure,其功能就是发送邮件,例如:

 

create or replace procedure ProcSendEmail 
IS 
SendorAddress Varchar2(30) := ; 
ReceiverAddress1 varchar2(30) := ; 
ReceiverAddress2 varchar2(30) := ;
EmailServer varchar2(30) := 'notes.oracle.com'; 
Port number := 25; 
 
conn UTL_SMTP.CONNECTION; 
 
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 ); 
 
mesg VARCHAR2( 4000 ); 
mesg_body varchar2(4000); 
txt varchar2(40) :='Datastore is stopped, pls start it!'; 
BEGIN 
 
conn:= utl_smtp.open_connection( EmailServer, Port ); 
utl_smtp.helo( conn, EmailServer ); 
utl_smtp.mail( conn, SendorAddress); 
utl_smtp.rcpt( conn, ReceiverAddress1); 
utl_smtp.rcpt( conn, ReceiverAddress2);
 
mesg:= 
'Content-Type: text/plain; Charset=GB2312' || crlf || 
'Date:' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf || 
'From:' || SendorAddress || crlf || 
'Subject: Datastore is stopped, pls start it!' || crlf || 
'To: '|| ReceiverAddress1||','||ReceiverAddress2|| crlf || 
'Content-Type: text/plain; Charset=GB2312' || crlf || 
'' || crlf || txt || crlf ; 
 
utl_smtp.data( conn, mesg ); 
utl_smtp.quit( conn ); 
 
END; 
/

当然改管理帐号要有对utl_smtp的execute权限,其中SendorAddress Varchar2(30) := ; 的mail地址是一个假的地址,写成这样的格式只是为了满足utl_smtp.mail的格式要求,通过@前的字符可以通过邮件区分发送邮件的数据库名称.

 

2.在该管理帐号建立对应用帐号下某个表的触发器,在该触发其中调用上述存储过程.当然该管理帐号要有create any trigger的权限,否则无法跨帐号来建立trigger. 要主意的是该trigger一定要经过严密测试且尽量简单,否则该trigger的失败将会导致应用对基表修改的失败,因为该trigger的动作已经和修改基表的动作结合形成了一个完整的事务.例如:

create or replace trigger mail_alert_trg
  after update on scott.datastoresetup
  for each row
begin
  IF ( :NEW.PARAMETER= 'DATASTORE_TERMINATE' AND :OLD.VALUE = 'N' AND :NEW.VALUE = 'Y') THEN
      PROCSENDEMAIL;
  end if;
end datastore_alert_trg;
/

 

3.注意事项:

utl_smtp虽然让oracle有了通过sql直接发送邮件的功能,但oracle涉及该功能的目的主要是为了报警功能,过多的使用该功能会到使得oracle的性能下降,尤其注意不要通过该功能来实现业务功能.

 

在oracle10g中对utl_smtp的功能通过新的包utl_mail进行了加强.

 

另外,如果需要将开关的状态发给应用程序,可以通过dbms_alert与trigger结合来实现,我在以前的项目中曾这样实现过,效果比较好.