Notify by Mail if any Streams Error Happened.

The requirement is if any streams error happened then mail will be automatically sent to us.

At first I tried by writing trigger. But trigger does not fit in this case, though I spent a considerable waste of time by writing trigger. Because the table is in SYS schema. And trigger can’t be created on SYS schema. I tried by creating view in strmadmin schema and failed to satisfy my requirement for lack of permission. So I proceed in alternative way.

The DBMS_JOB/ DBMS_SCHEDULER help me in this case a lot. With DBMS_SCHEDULER I created a job which will check periodically SYS.”_DBA_APPLY_ERROR” table and see any error row populated or not. If error occur then mail will be sent to user.

Up to today, I was busy in modifying the procedure in job_action attribute.
It is the code if any apply error happens on streams. It will notify by email.

create or replace procedure streams_proc as
sender VARCHAR2(200):='streams@bd.com';
recipient VARCHAR2(200):='arju@bd.com';
ccrecipient VARCHAR2(200):='dba@bd.com';
subject VARCHAR2(4000);
mail_conn utl_smtp.connection;
mailhost VARCHAR2(30) := 'mx.bd.com';
header VARCHAR2(1000);
variable1 varchar2(30000);
globalname varchar2(4000);
v_num number;
error_time date;
crlf VARCHAR2(2):= UTL_TCP.CRLF;
cursor c1 is select error_message,error_creation_time,source_database from DBA_APPLY_ERROR;
BEGIN
select global_name into globalname from global_name;
subject:='Streams Error Happened on '||globalname;
header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
'From: '||sender||''||crlf||
'Subject: '||subject||crlf||
'To: '||recipient||crlf||
'CC: '||ccrecipient;
select count(*) into v_num from SYS.DBA_APPLY_ERROR;
IF(v_num>0)
THEN
select max(ERROR_CREATION_TIME)into error_time from
SYS.DBA_APPLY_ERROR;
IF((sysdate-error_time)*24*60) 'job_strm_apply',
job_type => 'PLSQL_BLOCK',
job_action => 'begin STREAMS_PROC(); end;',
start_date => SYSDATE,
repeat_interval => 'FREQ = MINUTELY; INTERVAL = 1',
enabled=>TRUE);
END;
/