Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: September 21, 2003 - 9:50 pm UTC

Last updated: May 01, 2023 - 3:24 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Dear Tom:
I am testing with utl_smtp, my platform is oracle 8.1.7.0.0 + Windows Server 2000, my windows server is a member of dc, in our dc, we have exchange server as mail server, I issue the following command within sqlplus, it can work,

declare
v_connection utl_smtp.connection;
begin
v_connection :=utl_smtp.open_connection('mailsc2.test.com',25);
utl_smtp.helo(v_connection,'mailsc2.test.com');
utl_smtp.mail(v_connection,'jiangzx@test.com');
utl_smtp.rcpt(v_connection,'test@test.com');
utl_smtp.data(v_connection,'This mail is come from Oracle pl/sql');
utl_smtp.quit(v_connection);
end;
/

But when I try to send a mail to other mailbox not in our dc,
declare
v_connection utl_smtp.connection;
begin
v_connection :=utl_smtp.open_connection('mailsc2.test.com',25);
utl_smtp.helo(v_connection,'mailsc2.test.com');
utl_smtp.mail(v_connection,'jiangzx@test.com');
utl_smtp.rcpt(v_connection,'solaris350@sohu.com');
utl_smtp.data(v_connection,'This mail is come from Oracle pl/sql');
utl_smtp.quit(v_connection);
end;
/

System give me one error message as follow:
ORA-20002: 550 5.7.1 Unable to relay for solaris350@sohu.com
ORA-06512: at "SYS.UTL_SMTP", line 86
ORA-06512: at "SYS.UTL_SMTP", line 223
ORA-06512: at line 7

I wonder that maybe there are something wrong with the auth login, but I don't know how to deal with this issue, tks for you help in advance!



and Tom said...

you need to talk to your mail admin.

they have configured the smtp server so as to NOT relay (this has nothing to do with oracle's utl_smtp, you would be able to replicate this using simple TELNET or any email package that uses smtp to send mail -- like netscape or mozilla).

this is commonly done to prevent spammers from using your machine to send mail.

it is a configuration issue on the smtp side.

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

smtp auth login

Jackie, September 23, 2003 - 7:22 am UTC

Also I want to know whethere there is a function in utl_smtp to authentic the related mail account and password?
if there is, can you give me a example?

Tks a lot!

Tom Kyte
September 23, 2003 - 7:29 am UTC

you would have to look up the SMTP protocol and either use the utl_smtp command call or just "write_data" to talk to smtp directly.

I don't have an smtp server that requires authentication, so I cannot test.

but smtp is really "simple" as the name suggests. you can use TELNET to talk to your server. for example, in the following -- the bold is what I typed in:

[tkyte@localhost tkyte]$ telnet aria.us.oracle.com 25
Trying 138.2.5.51...
Connected to aria.us.oracle.com.
Escape character is '^]'.
220 aria.us.oracle.com ESMTP Sendmail 8.10.2+Sun/8.10.2; Tue, 23 Sep 2003 07:32:51 -0400 (EDT)
HELO aria
250 aria.us.oracle.com Hello dhcp-amer-vpn-gw2-west-141-144-79-157.vpn.oracle.com [141.144.79.157], pleased to meet you
HELP
214-2.0.0 This is sendmail version 8.10.2+Sun
214-2.0.0 Topics:
214-2.0.0 HELO EHLO MAIL RCPT DATA
214-2.0.0 RSET NOOP QUIT HELP VRFY
214-2.0.0 EXPN VERB ETRN DSN
214-2.0.0 For more info use "HELP <topic>".
214-2.0.0 To report bugs in the implementation contact Sun Microsystems
214-2.0.0 Technical Support.
214-2.0.0 For local information send email to Postmaster at your site.
214 2.0.0 End of HELP info
HELP EXPN
214-2.0.0 EXPN <recipient>
214-2.0.0 Expand an address. If the address indicates a mailing
214-2.0.0 list, return the contents of that list.
214 2.0.0 End of HELP info
QUIT
221 2.0.0 aria.us.oracle.com closing connection
Connection closed by foreign host.
[tkyte@localhost tkyte]$


You can telnet in and see what commands your server accepts and use the command api accordingly.

tks a lot!

Jackie, September 23, 2003 - 8:15 am UTC

Tks a lot, so I must adjust my smtp server to make it has the relay function to assure that I can send mail through oracle db to outside mailbox!

use of utl_smtp with user/password authentication

SHUBIN_DU, November 20, 2005 - 10:35 am UTC

if your server uses SASL AUTH LOGIN then you can use this calls of utl_smtp:

loc_reply := utl_smtp.open_connection( 'smtp.mysmtp.com', 25, loc_connection );
utl_smtp.ehlo( loc_connection, 'mydomain' );
utl_smtp.command( loc_connection, 'AUTH LOGIN' );
utl_smtp.command( loc_connection, 'my_user_in_BASE64' );
utl_smtp.command( loc_connection, 'my_password_in_BASE64' );
loc_recipient := 'myfriend@hissmtp.com';
loc_sender := 'me@mysmtp.com';
utl_smtp.rcpt ( loc_connection, loc_recipient );
utl_smtp.mail ( loc_connection, loc_sender );

and other smtp calls that you need

my_user_in_BASE64 is your user name encoded with BASE64
my_password_in_BASE64 is your password encoded with BASE64

you can get BASE64 encoder here: </code> http://makcoder.sourceforge.net/demo/base64.php <code>


Tom Kyte
November 21, 2005 - 8:21 am UTC

ops$tkyte@ORA9IR2> desc utl_encode<b>
FUNCTION BASE64_DECODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN
FUNCTION BASE64_ENCODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN</b>
FUNCTION QUOTED_PRINTABLE_DECODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN
FUNCTION QUOTED_PRINTABLE_ENCODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN
FUNCTION UUDECODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN
FUNCTION UUENCODE RETURNS RAW
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 R                              RAW                     IN
 TYPE                           BINARY_INTEGER          IN     DEFAULT
 FILENAME                       VARCHAR2                IN     DEFAULT
 PERMISSION                     VARCHAR2                IN     DEFAULT
 

UTL_SMTP

A reader, September 13, 2006 - 7:48 am UTC

Hi Sir,

I have implemented the same procedure send_mail, I have found in your book (concerning UTL_SMTP). Unfortunately when I tried to test it I got the following error:

ORA-20001 : Client was not authenticated.

Then, I have tried to enrich send_mail procedure by commands like:

utl_smtp.helo(l_mail_conn, l_mailhost); -- in send_mail
utl_smtp.command(l_mail_conn, 'AUTH LOGIN'); -- added
utl_smtp.command(l_mail_conn, 'user'); -- added
utl_smtp.command(l_mail_conn, 'pwd'); -- added

and this time I got the following error
ORA-20002 : 503 55 2 Send hello first

Could you please help me to fix this problem

Thanks in advance









Tom Kyte
September 13, 2006 - 2:40 pm UTC

send helo first maybe??

Issue with secure connection in utl_smtp

tanweer, September 19, 2006 - 5:40 am UTC

This helpful while u r performing secure connection with ult_smtp with public smtp server

Send Helo first

A reader, December 21, 2006 - 10:50 am UTC

Dear TOM,

What do you mean by send helo first.

I have tried all things possible and still I am faced with this strange Error : ORA-20002 : 503 55 2 Send hello first

Do you know what could be the reason of this error?

Thanks a lot

Tom Kyte
December 22, 2006 - 6:00 am UTC

maybe your smtp server wants to be friendly and exchange greetings first.

see the calls to helo above?

SMTP_UTL and GMT time

Abdul, January 04, 2007 - 3:19 pm UTC

Dear Mr. Tom,

We are using following procedure to send email from a database trigger, using UTL_SMTP. It works as indented but has a small problem. When sending messages, it seems that Oracle is sending GMT time to mail server rather than time from sysdate (Server is set to ¿US Central time¿). We don't want to hardcode the time difference (sysdate +/- GMT difference) in procedure because We have to change it when daylight saving time comes in effect and vise versa.

It will be highly appreciated if you can suggest any solution to overcome this issue.

Oracle Version 9.2.0.6
O/S. Windows 2003
Mail Server: Lotus Notes 6.5

-----------------------

CREATE OR REPLACE
PROCEDURE "SEND_EMAIL" (
p_recipient IN VARCHAR2,
p_message IN VARCHAR2 default ' ',
p_subject IN VARCHAR2 default ' ')

AS

p_sender varchar2(60) := 'myaddress@our_server.com';
crlf varchar2(2) := chr(13) || chr(10);
v_mailhost varchar2(30) := 'our_mailhost.com';
mail_conn utl_smtp.connection;

msg varchar2(5000):= 'Date: ' ||
to_char (sysdate, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: ' || p_sender || '<' || p_sender || '>' || crlf ||
'Subject: ' || p_subject || crlf ||
'To: ' || p_recipient || '<' || p_recipient || '>' || crlf ||
chr(10) || p_message;

BEGIN

mail_conn := utl_smtp.open_connection(v_mailhost, 25);
utl_smtp.helo(mail_conn, V_mailhost);
utl_smtp.mail(mail_conn, p_sender);
utl_smtp.rcpt(mail_conn, p_recipient);
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn, msg);
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(dbms_utility.format_error_stack);
dbms_output.put_line(dbms_utility.format_call_stack);

END;


One particular pattern is giving same issue

Bhavesh Gaur, April 08, 2009 - 4:37 am UTC

Hi Tom,
When I am using attached PL/SQL code, I am able to send the email except when my message string has the combination as shown below. If string has any other combination the mail goes off without any problem.

declare
mailhost VARCHAR2(100) := 'smtp.mail.com';
l_name VARCHAR2(100) := 'ABC';
l_email VARCHAR2(100) := 'abc@mail.com';
c_name VARCHAR2(100) := 'ABC';
c_email VARCHAR2(100) := 'abc@mail.com';
l_err_msg VARCHAR2(1000) := '';
mail_conn utl_smtp.connection;
l_crlf Varchar2(2) := CHR(13)||CHR(10);
l_start number := 1;
message varchar2(2000);
countdown number:=0;
BEGIN
message :='.'||l_crlf||'.'||l_crlf||'NCC022';
mail_conn :=utl_smtp.open_connection(mailhost,25);
utl_smtp.helo(mail_conn,mailhost);

utl_smtp.mail(mail_conn,c_email);
-- sender
utl_smtp.rcpt(mail_conn,l_email);
-- recipient
utl_smtp.open_data(mail_conn);

utl_smtp.write_data(mail_conn,'To: "'||l_name||'"
<'||l_email||'>'||utl_tcp.CRLF);
utl_smtp.write_data(mail_conn,'Date: '|| TO_CHAR( SYSDATE, 'dd Mon yy
hh24:mi:ss' ) ||utl_tcp.CRLF);
utl_smtp.write_data(mail_conn,'From: "'||c_name||'"
<'||c_email||'>'||utl_tcp.CRLF);

UTL_smtp.write_data(mail_conn, 'MIME-Version: ' || '1.0' ||
UTL_tcp.CRLF);


utl_smtp.write_data(mail_conn, 'Content-Type: text/html; charset=utf-8'
|| utl_tcp.CRLF);
utl_smtp.write_data( mail_conn, 'Content-Transfer-Encoding: 8bit' ||
UTL_tcp.CRLF);

utl_smtp.write_raw_data(mail_conn,
utl_raw.cast_to_raw(utl_tcp.CRLF||message||utl_tcp.CRLF) );
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);

EXCEPTION WHEN utl_smtp.transient_error OR utl_smtp.permanent_error
THEN
utl_smtp.quit(mail_conn);
NRIUTIL.ERROR_LOG ('NRIUTIL-012b', 'anbu', '', sqlerrm);
WHEN OTHERS THEN
NRIUTIL.ERROR_LOG ('NRIUTIL-013', 'anbu', '', sqlerrm);
END;


If message has '.'||l_crlf||'.'||l_crlf combination I get following error :

ERROR at line 1:
ORA-20001: 421 Service not available
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 356
ORA-06512: at line 58

Any idea why this is so bizzare ? Thanking you in advance.
Regards

Tom Kyte
April 13, 2009 - 1:21 pm UTC

     WHEN OTHERS THEN
      NRIUTIL.ERROR_LOG ('NRIUTIL-013', 'anbu', '', sqlerrm);


http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22

I hate your code



(but the smtp spec says "." on a line by itself is sufficient to terminate the conversation. look at this telnet example, for example

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2118740358226

and try sending your email using telnet directly with the .\r\n.\r\n and see what happens....



[tkyte@dellpe ~]$ <b>telnet localhost 25</b>
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 dellpe.localdomain ESMTP Sendmail 8.13.1/8.13.1; Mon, 13 Apr 2009 13:05:18 -0400<b>
HELO localhost</b>
250 dellpe.localdomain Hello localhost.localdomain [127.0.0.1], pleased to meet you<b>
MAIL FROM: tkyte@localhost</b>
250 2.1.0 tkyte@localhost... Sender ok<b>
RCPT TO: tkyte@localhost</b>
250 2.1.5 tkyte@localhost... Recipient ok<b>
DATA</b>
354 Enter mail, end with "." on a line by itself
hello tom
.
250 2.0.0 n3DH5Iw8023610 Message accepted for delivery
.
500 5.5.1 Command unrecognized: "."



See how after DATA - it (smtp) says back to us "end with a '.'"

that is SMTP - not UTL_SMTP. You need to send standard emails conforming to the rules that SMTP imposes.

Error ora-29278

andry, September 03, 2016 - 12:31 pm UTC

when I run following snippets return " ora-29278 SMTP transient error : 421 Service not available ".

declare
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection('smtp@silverdb.net', 465);
UTL_SMTP.helo(l_mail_conn, 'smtp@silverdb.net');
UTL_SMTP.mail(l_mail_conn, 'andry@silverdb.net');
UTL_SMTP.rcpt(l_mail_conn, 'smith@silverdb.net');
UTL_SMTP.data(l_mail_conn, 'msg' || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;

Error:-
ora-29278 SMTP transient error : 421 Service not available
Connor McDonald
September 04, 2016 - 1:20 pm UTC

"421 Service not available" is a OS level error (google it - you'll see heaps of examples). Basically either something at the network layer or software layer blocked or killed your access to your mail server.

Error compilation

A reader, October 17, 2019 - 1:29 pm UTC

Hello, i have error in compliation code
Connor McDonald
October 21, 2019 - 6:25 am UTC

OK

Unable to send email from 18xe PDB

Srinivas, April 21, 2023 - 4:08 pm UTC

1) Connected to DB
2) Changed session to XEPDB1
3) Created ACLs like below
exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('send_mail.xml','Allow mail to be send', 'APPUSER', TRUE, 'connect');
commit;
----Grant the connect and resource privilege as below
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'connect');
exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('send_mail.xml','APPUSER',TRUE, 'resolve');
exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('send_mail.xml','<Server IP>',587);

4) Code snippet
BEGIN
execute immediate 'alter session set smtp_out_server=''smtp.office365.com:587''';
UTL_MAIL.SEND(sender=>'srinivas.p@company.com',
recipients=>'srinivas.p@company.com',
subject=>'Test Mail',
message=>'This is just a test mail');
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'The following error:'||sqlerrm);
END;
/
Connor McDonald
May 01, 2023 - 3:24 pm UTC

You might have missed out some critical piece of information for us ....

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library