Email Notifytion When Oracle Stream Replication Fail

All object in schema FRAME are replicated by Oracle Stream Replication. I create a job to check the data in table FRAME.SYS_STREAM_REPLICATION if they are same in source database and destination database.

If the data are different, use UTL_MAIL to send an email to me .

create table FRAME.SYS_STREAM_REPLICATION
(
  COL      NUMBER,
  SMS_SENT NUMBER
);

comment on table FRAME.SYS_STREAM_REPLICATION
  is '数据库同步测试表';
comment on column FRAME.SYS_STREAM_REPLICATION.SMS_SENT
  is '1 已发送,  0未发送';
grant select, insert, update, delete on FRAME.SYS_STREAM_REPLICATION to STREAMADMIN;

/* 检测数据库复制是否正常 */
declare
  src_value number;
  dest_value number;
  sms_sent number;
  err varchar2(3000);
BEGIN
  select col,sms_sent into src_value,sms_sent from frame.sys_stream_replication;
  select col into dest_value from frame.sys_stream_replication@dest_db;
  if src_value<> dest_value then  
     if sms_sent<>'1' then 
        UTL_MAIL.send(sender => 'name@domain.com',
            recipients => 'name@domain.com',
               subject => 'Database Stream Replication Fail',
               message => 'Hello World',
             mime_type => 'text; charset=us-ascii');
          
         insert into frame.sms_info_prepare_send  (sms_id,mobile_no,sms_content) values ('999'||src_value,'18912345678','Oracle Stream Replication Fail!');
         update  frame.sys_stream_replication set sms_sent='1';
     end if;
   else
     update frame.sys_stream_replication set col=col+1, sms_sent='0';

   end if;
   commit;
  EXCEPTION
  WHEN OTHERS THEN
    err := SQLERRM;
    ROLLBACK;    
      INSERT INTO job_log
             (date_time, err, remark)
      VALUES
             (sysdate,  err, '检测数据库复制是否正常 job 65');
  COMMIT;
END;