Home » Server Options » Streams & AQ » Mail based on Apply Error
Mail based on Apply Error [message #292494] Wed, 09 January 2008 01:20 Go to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

My target is in stream environment when an apply error will be occurred it will mail to me.How I can proceed.

dba_applY_error view is there . Which is from _DBA_APPLY_ERROR, and from streams$_apply_process but I can't write a trigger (which will mail to me when any row is populated) on these tables/view because they are belong to sys.

How I can achieve my goal.
Re: Mail based on Apply Error [message #292512 is a reply to message #292494] Wed, 09 January 2008 01:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it directly.
You can create a job that periodically checks dba_apply_error.

Regards
Michel
Re: Mail based on Apply Error [message #292565 is a reply to message #292494] Wed, 09 January 2008 04:00 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thank you so much Michel. I have created job using DBMS_SCHEDULER.
Now problem is in mail content.

utl_smtp.data(mail_conn,'Streams_Error_Happened'); then it show in mailbox 'Streams_Error_Happened'and it is ok. But instead of it if I want to print a variable's content of varchar2 by
utl_smtp.data(mail_conn,variable1); then mail message body becomes null instead of the variable's real value.

The value of variable1 content is not null which does not reflect in mail message body.

Any help.
Re: Mail based on Apply Error [message #292569 is a reply to message #292565] Wed, 09 January 2008 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Without the actual code it is impossible to answer.

Regards
Michel
Re: Mail based on Apply Error [message #292572 is a reply to message #292494] Wed, 09 January 2008 04:22 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

If code is this one,

declare
 mail_conn utl_smtp.connection;
 variable1 varchar2(30000);
 v_num number;
 cursor c1 is select error_message from SYS."_DBA_APPLY_ERROR";
 BEGIN
 select count(*)  into v_num  from SYS."_DBA_APPLY_ERROR";
 IF(v_num>0)
 THEN
 for item in c1 loop
 variable1:=variable1|| item.error_message;
 end loop;
 dbms_output.put_line(variable1);
 mail_conn :=utl_smtp.open_connection('mx.bdbd.com',25);
 utl_smtp.helo(mail_conn,'mx.bdbd.com');
 utl_smtp.mail(mail_conn,'test@bdbd.com');
 utl_smtp.rcpt(mail_conn,'arju@bdbd.com');
 utl_smtp.data(mail_conn,variable1); 
 utl_smtp.quit(mail_conn);
 ELSE return;
 END IF;
 END;
 /


Then in my mailbox I get an empty mail.

If I change

utl_smtp.data(mail_conn,variable1);
to

utl_smtp.data(mail_conn,'Stream Error')
then in my mailbox
I get message 'Streams Error'.

I notice on the first case, I want to get the content of variable1 in my mail box.
Re: Mail based on Apply Error [message #292586 is a reply to message #292572] Wed, 09 January 2008 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you sure variable1 is not null?

By the way, counting before is useless, just loop on your cursor (which is not the fastest way) and check variable1 content.

Regards
Michel
Re: Mail based on Apply Error [message #292808 is a reply to message #292494] Wed, 09 January 2008 22:34 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Sorry Michel if I do anything wrong.

>Are you sure variable1 is not null?
--By writing PL/sql code variable1 returns value.

>counting before is useless
-- I counted beacause if there is no rows in "_dba_apply_error" then it will not enter in the loop. i.e if no apply rows it will not mail to me.


Here is the PL/SQL statement of DBMS_Scheduler

SQL> declare
 mail_conn utl_smtp.connection;
 variable1 varchar2(30000);
 v_num number;
 cursor c1 is select error_message from SYS."_DBA_APPLY_ERROR";
 BEGIN
 select count(*)  into v_num  from SYS."_DBA_APPLY_ERROR";
 IF(v_num>0)
 THEN
 for item in c1 loop
 variable1:=variable1|| item.error_message;
 end loop;
 dbms_output.put_line(variable1);
 mail_conn :=utl_smtp.open_connection('mx.bd.com',25);
 utl_smtp.helo(mail_conn,'mx.bd.com');
 utl_smtp.mail(mail_conn,'test@bd.com');
 utl_smtp.rcpt(ma  2    3    4    5    6  il_conn,'arju@bd.com');
 utl_smtp.data(mail_conn,variable1);
 utl_smtp.quit(mail_conn);
 ELSE return;
 END IF;
 END;
 /  7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23
ORA-26753: Mismatched columns found in 'PROD.MODULE'
ORA-02291: integrity
constraint (PROD.FKCF5E5C75480D2) violated - parent key not found
ORA-02291:
integrity constraint (PROD.FK6AA710312FFF947) violated - parent key not
found
ORA-02291: integrity constraint (PROD.FK1E63CEFF9EA15BCD) violated -
parent key not found
ORA-02291: integrity constraint (PROD.FK1E63CEFF9EA15BCD)
violated - parent key not found
ORA-26753: Mismatched columns found in
'PROD.MODULE'
ORA-26753: Mismatched columns found in
'PROD7.MODULE'
ORA-26753: Mismatched columns found in
'PROD.MODULE'
.
.
.
.
PL/SQL procedure successfully completed.



Here variable1 content is returned.

[Updated on: Wed, 16 January 2008 22:37]

Report message to a moderator

Re: Mail based on Apply Error [message #292820 is a reply to message #292808] Wed, 09 January 2008 23:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is very hard to read with that line numbers interleaved with code.

And what if there is no row and not counted? You will still not enter the loop!

I don't what is the problem. Try to do it with only 1 message to start.

Regards
Michel
Re: Mail based on Apply Error [message #292827 is a reply to message #292494] Thu, 10 January 2008 00:09 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

The actual PL/SQL code is,

 declare
 2 mail_conn utl_smtp.connection;
 3 variable1 varchar2(30000);
 4 v_num number;
 5 cursor c1 is select error_message from SYS."_DBA_APPLY_ERROR";
 6 BEGIN
 7 select count(*)  into v_num  from SYS."_DBA_APPLY_ERROR";
 8 IF(v_num>0)
 9   THEN
 10     for item in c1 loop
 11     variable1:=variable1|| item.error_message;
 12     end loop;
 13     dbms_output.put_line(variable1);
 14     mail_conn:=utl_smtp.open_connection('mx.bd.com',25);
 15     utl_smtp.helo(mail_conn,'mx.bd.com');
 16     utl_smtp.mail(mail_conn,'test@bd.com');
 17     utl_smtp.rcpt(mail_conn,'arju@bd.com');
 18     utl_smtp.data(mail_conn,variable1);
 19     utl_smtp.quit(mail_conn);
 20  ELSE return;
 21  END IF;
 22 END;
  / 


If I don't keep IF(v_num>0) checking then whether rows found or not in SYS."_DBA_APPLY_ERROR" mail will be sent. I kept checking because if no rows found row found error will not be returned.

In this PL/SQL there is no compile error. And it execute successfully and also return result correctly except this line.
utl_smtp.data(mail_conn,variable1);


By this line it is expected that variable1 content will be mailed to my account but my mail body remain empty. If instead of variable1 I put any string like "ERROR OCCURED" then mail content become ok.

My question is why it does not mail body to my account with the content of variable1. (why empty message come when I use variable).

Michel have you got my point.

[Updated on: Wed, 16 January 2008 22:37]

Report message to a moderator

Re: Mail based on Apply Error [message #292861 is a reply to message #292827] Thu, 10 January 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

IF(v_num>0) checking then whether rows found or not in SYS."_DBA_APPLY_ERROR" mail will be sent.

You missed one point in my answer: "and check variable1 content" I meant check if it is null or not.

Quote:

Michel have you got my point.

You didn't got mine.
I said take only the first message and make a try.
Does this work?

Regards
Michel
Re: Mail based on Apply Error [message #292899 is a reply to message #292494] Thu, 10 January 2008 02:35 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thank you Michel for the prompt answer, At first time with only one row I tried and I failed. Again here the result.

 SQL> declare
 mail_conn utl_smtp.connection;
 variable1 varchar2(4000);
 v_num number;
 BEGIN
 select count(*)  into v_num  from SYS."_DBA_APPLY_ERROR";
 IF(v_num>0)
 THEN
 select  error_message  into variable1 from SYS."_DBA_APPLY_ERROR"   where rownum=1 ;
 dbms_output.put_line('The Error is ' ||variable1);
 mail_conn :=utl_smtp.open_connection('mx.bd.com',25);
 utl_smtp.helo(mail_conn,'mx.bd.com');
 utl_smtp.mail(mail_conn,'test@bd.com');
 utl_smtp.rcpt(mail_conn,'arju@bd.com');
 utl_smtp.data(mail_conn,variable1);
 utl_smtp.quit(mail_conn);
 ELSE return;
 END IF;
 END;  

 /
The Error is ORA-02291: integrity constraint (PROD.FK1E63CEFF9EA15BCD) violated
- parent key not found


PL/SQL procedure successfully completed.



Here variable1 content displayed but in mail body blank message I get.

[Updated on: Wed, 16 January 2008 22:40]

Report message to a moderator

Re: Mail based on Apply Error [message #292914 is a reply to message #292899] Thu, 10 January 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check if there is no character CHR(0) in the string.
Output "dump(error_message)".

Regards
Michel
Re: Mail based on Apply Error [message #292927 is a reply to message #292494] Thu, 10 January 2008 03:20 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select dump(error_message) from SYS."_DBA_APPLY_ERROR"   where rownum=1 ;

DUMP(ERROR_MESSAGE)
--------------------------------------------------------------------------------
Typ=1 Len=91: 79,82,65,45,48,50,50,57,49,58,32,105,110,116,101,103,114,105,116,1
21,32,99,111,110,115,116,114,97,105,110,116,32,40,80,82,79,68,55,46,70,75,49,69,
54,51,67,69,70,70,57,69,65,49,53,66,67,68,41,32,118,105,111,108,97,116,101,100,3
2,45,32,112,97,114,101,110,116,32,107,101,121,32,110,111,116,32,102,111,117,110,
100,10
Re: Mail based on Apply Error [message #292930 is a reply to message #292927] Thu, 10 January 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just a try: trim chr(10) characters.

Regards
Michel
Re: Mail based on Apply Error [message #292933 is a reply to message #292494] Thu, 10 January 2008 03:35 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select trim(chr(10) from error_message) from SYS."_DBA_APPLY_ERROR"   where rownum=1 ;

TRIM(CHR(10)FROMERROR_MESSAGE)
--------------------------------------------------------------------------------
ORA-02291: integrity constraint (PROD.FK1E63CEFF9EA15BCD) violated - parent key
 not found

SQL> select error_message from SYS."_DBA_APPLY_ERROR"   where rownum=1 ;

ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-02291: integrity constraint (PROD.FK1E63CEFF9EA15BCD) violated - parent key
 not found

SQL> select dump(trim(chr(10) from  error_message) )  from SYS."_DBA_APPLY_ERROR"   where rownum=1 ;

DUMP(TRIM(CHR(10)FROMERROR_MESSAGE))
--------------------------------------------------------------------------------
Typ=1 Len=90: 79,82,65,45,48,50,50,57,49,58,32,105,110,116,101,103,114,105,116,1
21,32,99,111,110,115,116,114,97,105,110,116,32,40,80,82,79,68,55,46,70,75,49,69,
54,51,67,69,70,70,57,69,65,49,53,66,67,68,41,32,118,105,111,108,97,116,101,100,3
2,45,32,112,97,114,101,110,116,32,107,101,121,32,110,111,116,32,102,111,117,110,
100

[Updated on: Wed, 16 January 2008 22:38]

Report message to a moderator

Re: Mail based on Apply Error [message #292934 is a reply to message #292494] Thu, 10 January 2008 03:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Here length is reduced by 1 to 90.
Re: Mail based on Apply Error [message #292951 is a reply to message #292934] Thu, 10 January 2008 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is try to send the trimmed message.

Regards
Michel
Re: Mail based on Apply Error [message #292952 is a reply to message #292494] Thu, 10 January 2008 04:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes, I tried. Message is sent and mail is received but it is empty. No Message Body.
Re: Mail based on Apply Error [message #294264 is a reply to message #292494] Thu, 17 January 2008 00:27 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

This was not done as because of UTL_MAIL.DATA contents conform to
MIME(RFC822) specification. And according to the specification, body is terminated by CR.LF specification. Just adding UTL_TCP.CRLF before variable1 solved the problem.
Re: Mail based on Apply Error [message #294276 is a reply to message #294264] Thu, 17 January 2008 00:58 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: streams database view
Next Topic: Database link on Oracle Streams
Goto Forum:
  


Current Time: Thu Mar 28 17:09:25 CDT 2024