| sending email outsude the domain via database [message #609689] |
Tue, 11 March 2014 05:30  |
 |
arhaan
Messages: 5
Registered: March 2014
Location: MALAYSIA
|
Junior Member |
|
|
Dear Friends,
iam trying to send the mail outside the domain by below code everytime iam getting error,please let me know the solution for this error.
DECLARE
v_From VARCHAR2(80) := 'vaishnav@cds.com';--'arhaan6c@cds.com';
v_Recipient VARCHAR2(80) := 'arhaan6c@gmail.com';--'vaishnav@cds.com';
v_Subject VARCHAR2(80) := Subject;--'test subject';
v_Mail_Host VARCHAR2(30) := '1.80.1.2';--'cds-mail.CDSCOMP';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
message VARCHAR2(10000) := 'test send mail ';
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host,25);
utl_smtp.command( v_Mail_Conn,v_Mail_Host); --- mail server name
utl_smtp.command( v_Mail_Conn, 'AUTH LOGIN');
utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( v_From ))) ); --- username
utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'xxxxxx' ))) ); ----- password
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="mmm.htm"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="mmm.htm"'|| crlf ||
crlf ||
'Hi vaishnav'|| crlf ||
message|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
<<no_setup>>
null;
END ;
/
and the error iam getting is
ORA-20000: Unable to send mail: ORA-29279: SMTP permanent error: 500 5.3.3 Unrecognized command
ORA-06512: at line 64
please give me an example i could not find exact solution for this problem.
thankyou very much in advance...
|
|
|
|
|
|
| Re: sending email outsude the domain via database [message #609695 is a reply to message #609689] |
Tue, 11 March 2014 05:47  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your code as posted does not run. I've debugged and formatted it, and I get a different error:
orclz>
orclz> DECLARE
2 v_from VARCHAR2(80) := 'vaishnav@cds.com'; --'arhaan6c@cds.com';
3 v_recipient VARCHAR2(80) := 'arhaan6c@gmail.com'; --'vaishnav@cds.com';
4 v_subject VARCHAR2(80) := 'test subject';
5 v_mail_host VARCHAR2(30) := '1.80.1.2'; --'cds-mail.CDSCOMP';
6 v_mail_conn utl_smtp.connection;
7 crlf VARCHAR2(2) := Chr(13)
8 ||Chr(10);
9 message VARCHAR2(10000) := 'test send mail ';
10 BEGIN
11 v_mail_conn := utl_smtp.Open_connection(v_mail_host, 25);
12
13 utl_smtp.Command(v_mail_conn, v_mail_host); --- mail server name
14
15 utl_smtp.Command(v_mail_conn, 'AUTH LOGIN');
16
17 utl_smtp.Command(v_mail_conn, utl_raw.Cast_to_varchar2(
18 utl_encode.Base64_encode(
19 utl_raw.Cast_to_raw(v_from)))); --- username
20
21 utl_smtp.Command(v_mail_conn, utl_raw.Cast_to_varchar2(
22 utl_encode.Base64_encode(
23 utl_raw.Cast_to_raw('xxxxxx'))
24 )); ----- password
25
26 utl_smtp.Helo(v_mail_conn, v_mail_host);
27
28 utl_smtp.Mail(v_mail_conn, v_from);
29
30 utl_smtp.Rcpt(v_mail_conn, v_recipient);
31
32 utl_smtp.Data(v_mail_conn, 'Date: '
33 || To_char(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss')
34 || crlf
35 || 'From: '
36 || v_from
37 || crlf
38 || 'Subject: '
39 || v_subject
40 || crlf
41 || 'To: '
42 || v_recipient
43 || crlf
44 || 'MIME-Version: 1.0'
45 || crlf
46 || -- Use MIME mail standard
47 'Content-Type: multipart/mixed;'
48 || crlf
49 || ' boundary="-----SECBOUND"'
50 || crlf
51 || crlf
52 || '-------SECBOUND'
53 || crlf
54 || 'Content-Type: text/plain;'
55 || crlf
56 || 'Content-Transfer_Encoding: 7bit'
57 || crlf
58 || crlf
59 || 'some message text'
60 || crlf
61 || -- Message body
62 'more message text'
63 || crlf
64 || crlf
65 || '-------SECBOUND'
66 || crlf
67 || 'Content-Type: text/plain;'
68 || crlf
69 || ' name="mmm.htm"'
70 || crlf
71 || 'Content-Transfer_Encoding: 8bit'
72 || crlf
73 || 'Content-Disposition: attachment;'
74 || crlf
75 || ' filename="mmm.htm"'
76 || crlf
77 || crlf
78 || 'Hi vaishnav'
79 || crlf
80 || message
81 || crlf
82 || -- Content of attachment
83 crlf
84 || '-------SECBOUND--' -- End MIME mail
85 );
86
87 utl_smtp.Quit(v_mail_conn);
88 END;
89
90 /
DECLARE
*
ERROR at line 1:
ORA-29278: SMTP transient error: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 54
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_SMTP", line 197
ORA-06512: at line 11
orclz>
Can you test again, and this time please use copy/paste to show what happened. Also, please format your your code and enclose it within [code] tags, as described here How to use [code] tags and make your code easier to read
|
|
|
|
| Re: sending email outsude the domain via database [message #609696 is a reply to message #609695] |
Tue, 11 March 2014 05:49 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Yes, the problem is clear: that address you are using (somewhere in China?) is not listening on port 25:c:\users\john>telnet 1.80.1.2 25
Connecting To 1.80.1.2...Could not open connection to the host, on port 25: Connect failed
c:\users\john> |
|
|
|
| Re: sending email outsude the domain via database [message #609704 is a reply to message #609696] |
Tue, 11 March 2014 07:17 |
|
arhaan
Messages: 5
Registered: March 2014
Location: MALAYSIA
|
Junior Member |
|
|
DECLARE
v_From VARCHAR2(80) := 'zain@cdscomp.com';--'sri.vani@cdscomp.com';
v_Recipient VARCHAR2(80) := 'zain@gmail.com';--'sri.vani@idscomp.com';
v_Subject VARCHAR2(80) := Subject;--'test subject';
v_Mail_Host VARCHAR2(30) := '1.50.1.9';--'cds-mail.CDSCOMP';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
message VARCHAR2(10000) := 'test send mail ';
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host,25);
utl_smtp.command( v_Mail_Conn,v_Mail_Host); --- mail server name
utl_smtp.command( v_Mail_Conn, 'AUTH LOGIN');
utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( v_From ))) ); --- username
utl_smtp.command( v_Mail_Conn, utl_raw.cast_to_varchar2( utl_encode.base64_encode( utl_raw.cast_to_raw( 'xxxxxx' ))) ); ----- password
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || v_Recipient || crlf ||
'MIME-Version: 1.0'|| crlf || -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
'some message text'|| crlf || -- Message body
'more message text'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="mmm.htm"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="mmm.htm"'|| crlf ||
crlf ||
'Hi Mohammed Thanssef'|| crlf ||
message|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--' -- End MIME mail
);
utl_smtp.Quit(v_mail_conn);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);
END;
<<no_setup>>
null;
END ;
/
iam still getting the same error, i check the SMTP service in oracle server its on and running
ORA-20000: Unable to send mail: ORA-29279: SMTP permanent error: 500 5.3.3 Unrecognized command
ORA-06512: at line 64
|
|
|
|
| Re: sending email outsude the domain via database [message #609707 is a reply to message #609704] |
Tue, 11 March 2014 07:29 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
This is NOT a copy paste. How do I know? Because that code will not run. And it isn't formatted.
Do you not realize that the way you are posting this problem makes it impossible to fix? Look at my post: the error is obvious, reported as being on line 11. Look at your post: it doesn't even have line numbers.
|
|
|
|
| Re: sending email outsude the domain via database [message #609709 is a reply to message #609704] |
Tue, 11 March 2014 07:30 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Michel Cadot wrote on Tue, 11 March 2014 11:39
Please do search, this issue has already been addressed here.
Note that the problem does not reside on Oracle side but in your code formatting the message as the error "5.3.3 Unrecognized command" points it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
