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

Mail within Oracle Database.

UTL_SMTP package is dedicated for sending email over SMTP. With this package it is quite easy to send e-mail to a specified user. It is easy to include a body of the message within email message body. But my requirement was, from a database table row will be retrieved and that row after analyze will be mailed to a user. It was not so easy for me and it sucked me significant time to do this. It was because body message is formatted by MIME(RFC822) specification. When a row is retrieved and I mailed that row I got a blank mail in my message body.The DATA routine will terminate the message with a . sequence (a single period at the beginning of a line), as required by RFC821. And every time I send mail at the first line it is terminated. It will also translate any sequence of . (single period) in body to .. (double period). So,I added with the message body UTL_TCP.CRLF

Then everything went ok.