Home » Server Options » Streams & AQ » Mail based on Apply Error
|
|
|
|
|
|
|
|
| Re: Mail based on Apply Error [message #292572 is a reply to message #292494] |
Wed, 09 January 2008 04:22   |
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);
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 |
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 #292827 is a reply to message #292494] |
Thu, 10 January 2008 00:09 |
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 #292899 is a reply to message #292494] |
Thu, 10 January 2008 02:35 |
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 #292927 is a reply to message #292494] |
Thu, 10 January 2008 03:20 |
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 #292933 is a reply to message #292494] |
Thu, 10 January 2008 03:35 |
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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jan 11 00:16:52 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
