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;
