Home » Server Options » Streams & AQ » Mail based on Apply Error
| Mail based on Apply Error [message #292494] |
Wed, 09 January 2008 01:20  |
|
|
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 #292565 is a reply to message #292494] |
Wed, 09 January 2008 04:00  |
|
|
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 #292572 is a reply to message #292494] |
Wed, 09 January 2008 04:22 |
|
|
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);
utl_smtp.data(mail_conn,'Stream Error')
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 #292808 is a reply to message #292494] |
Wed, 09 January 2008 22:34 |
|
|
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 #292827 is a reply to message #292494] |
Thu, 10 January 2008 00:09 |
|
|
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 #292899 is a reply to message #292494] |
Thu, 10 January 2008 02:35 |
|
|
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 #292927 is a reply to message #292494] |
Thu, 10 January 2008 03:20 |
|
|
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 #292933 is a reply to message #292494] |
Thu, 10 January 2008 03:35 |
|
|
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 #294264 is a reply to message #292494] |
Thu, 17 January 2008 00:27 |
|
|
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.
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 25 19:56:34 CDT 2013
Total time taken to generate the page: 0.30236 seconds
|
] 
Members
Search
Help
Register
Login
Home
