Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Balasubramanian.

Asked: December 03, 2001 - 8:27 am UTC

Last updated: April 19, 2012 - 5:53 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I created the procedure send_mail as in your book in the user System.

create or replace procedure send_mail
( p_sender in varchar2,
p_recipient in varchar2,
p_message in varchar2 )
as
l_mailhost varchar2(255) := '10.228.1.75' ;
l_mail_conn utl_smtp.connection ;
begin
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25) ;
utl_smtp.helo(l_mail_conn, l_mailhost) ;
utl_smtp.mail(l_mail_conn, p_sender) ;
utl_smtp.rcpt(l_mail_conn, p_recipient ) ;
utl_smtp.open_data(l_mail_conn) ;
utl_smtp.write_data(l_mail_conn, p_message ) ;
utl_smtp.close_data(l_mail_conn) ;
utl_smtp.quit(l_mail_conn );
dbms_output.put_line('Message send to the user successfully') ;
end ;

The procedure compiled without any error. I call the above procedure in the same user as :

begin
send_mail('vrbala@visto.com',
'vr_bala@yahoo.com',
'This is the test message from oracle server' ) ;
end ;

I got the following error. What should i do to run this.

ORA-20002: 554 Relay rejected for policy reasons.
ORA-06512: at "SYS.UTL_SMTP", line 86
ORA-06512: at "SYS.UTL_SMTP", line 223
ORA-06512: at "SYSTEM.SEND_MAIL", line 12
ORA-06512: at line 2

Thanks,
Bala

and we said...

Talk to your system and or network administrator. This is 100% an smtp configuration issue. Your host (10.228.1.75) is not permitted to relay email (to prevent spammers from using you as a jump off point). You need to find the valid smtp server within your organization.

Rating

  (25 ratings)

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

Comments

Thanks

Balasubramanian, December 03, 2001 - 8:51 am UTC

Thanks for immediate response. Also is there any way to
check validity of smtp server using oracle procedures.
Because the administrator told this is the correct smpt
server.

Thanks,
Bala

Tom Kyte
December 03, 2001 - 9:44 am UTC

You did check the "validity" of the SMTP server -- you got the message back "relaying not allowed". Your smtp server is simply NOT allowed to relay to hosts outside of its domain. You can telnet right to the SMTP server to prove this to yourself.

The elements in bold are what you type in, please use your hostname of course. You should get the same output as you did from Oracle -- indicating simply that your server in visto.com is not allowed to relay to yahoo.com


$ telnet aria-dev 25
Trying 138.2.5.52...
Connected to aria-dev.us.oracle.com.
Escape character is '^]'.
220 aria-dev.us.oracle.com ESMTP Sendmail 8.9.3+Sun/8.9.1; Mon, 3 Dec 2001 09:41:19 -0500 (EST)
helo aria-dev
250 aria-dev.us.oracle.com Hello aria-dev.us.oracle.com [138.2.5.52], pleased to meet you
mail from: tkyte@us.oracle.com
250 tkyte@us.oracle.com... Sender ok
rcpt to: Thomas.Kyte@oracle.com
250 Thomas.Kyte@oracle.com... Recipient ok
data
354 Enter mail, end with "." on a line by itself
this is a test
.

250 JAA17566 Message accepted for delivery
quit
221 aria-dev.us.oracle.com closing connection
Connection closed by foreign host.


Relaying Denied fixes

Andy Finkenstadt, December 03, 2001 - 3:40 pm UTC

Tell your system adminstrator to allow the IP address for the database server initiating the email to relay, by adding the address to (typically) /etc/mail/relay-domains.

The relay prohibition comes most restrictively from that lack, or from not using an @locally.relayable.domain sender address if the administration is using FEATURE(relay_local_from).


Ora-20001 :450 when executing procedure.

Vivek Sharma, March 31, 2003 - 3:32 am UTC

Hi Tom,

I am receiveing ora-20001 :450 error while executing a procedure. But no help is provided on Metalink for this. The content of my procedure is

PROCEDURE mail_job_check IS
v_msg varchar2 (10000);
mailhost varchar2(30) := '172.6.49.8' ;
mail_conn utl_smtp.connection;
crlf varchar2(2):= CHR(13) || CHR(10);
mesg varchar2(20000);
c_mesg varchar2(20000);
a_mesg varchar2(20000);
mail_id user_info.ui_email%type;
v_isin varchar2(10000);
c_isin varchar2(10000);
a_isin varchar2(10000);
v_from_dt date;

Cursor c1 is
Select ui_user_cd
from user_info
where ui_active = 'N'
and to_char(ui_last_upd_dt,'dd-mm-yyyy')= to_char(sysdate,'dd-mm-yyyy'); BEGIN SELECT ui_email
INTO mail_id
FROM user_info
WHERE ui_user_cd='SFG';
For x in c1 loop
a_mesg:=a_mesg||crlf||x.ui_user_cd;
end loop;
IF a_mesg IS NULL THEN
a_mesg := 'No Users Deactivated Now '||to_char(sysdate,'dd-mm-yyyy');
END IF;
mail_conn := utl_smtp.open_connection(mailhost, 25); utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn,'vivek.sharma@pidilite.com');
utl_smtp.rcpt(mail_conn,mail_id);
utl_smtp.rcpt(mail_conn,'vivek.sharma@pidilite.com');
mesg:= 'Date: '||TO_CHAR(SYSDATE,'DD MON YYYY HH24:MI:SS' )|| crlf || 'From: CLMS Software Team '|| crlf || 'Subject: Users deactivated on '||sysdate|| crlf|| a_mesg;
utl_smtp.data(mail_conn,mesg);
utl_smtp.quit(mail_conn);
END;

When I execute this procedure, I get an error as

BEGIN clms.mail_job_check; END;

*
ERROR at line 1:
ORA-20001: 450 4.7.1 abhi_s@@pidilite.com... Relaying temporarily
denied. Cannot resolve PTR record for 10.16.0.75
ORA-06512: at "SYS.UTL_SMTP", line 83
ORA-06512: at "SYS.UTL_SMTP", line 223
ORA-06512: at "CLMS.MAIL_JOB_CHECK", line 34
ORA-06512: at line 1

Please suggest a workaround.

Thanks in advance.

Tom Kyte
March 31, 2003 - 8:38 am UTC

workaround: read the error message.

You are getting apparently a 450 error from your SMTP server (the ora-20001 is utl_smtp's way to saying "error encountered -- here is more information"


If you just:

[tkyte@tkyte-pc Desktop]$ telnet 172.6.49.8 25
Trying 172.6.49.8...
Connected to xxxx.
Escape character is '^]'.
220 aria.us.oracle.com ESMTP Sendmail 8.10.2+Sun/8.10.2; Mon, 31 Mar 2003 08:35:55 -0500 (EST)
HELO <hostname -- whatever the hostname of 172.6.49.8 is>
250 aria.us.oracle.com Hello tkyte-pc.us.oracle.com [138.1.120.255], pleased to meet you
MAIL FROM: vivek.sharma@pidilite.com
250 2.1.0 vivek.sharma@pidilite.com... Sender ok
RCPT TO: vivek.sharma@pidilite.com
250 2.1.5 vivek.sharma@pidilite.com... Recipient ok
DATA
354 Enter mail, end with "." on a line by itself
hello tom
.

250 2.0.0 h2VDb7229862 Message accepted for delivery
QUIT
221 2.0.0 aria.us.oracle.com closing connection
Connection closed by foreign host.
[tkyte@tkyte-pc Desktop]$



and you just enter the stuff in BOLD, you'll reproduce the same exact error. This is a SMTP configuration issue -- you need to talk to your email guys.

utl_smtp strangeness 9.2

Ray, April 14, 2003 - 4:23 pm UTC

declare
mailhost varchar2(30) := '999.11.2.99' ;
mail_conn utl_smtp.connection;
mesg varchar2(4000);
begin
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);

utl_smtp.mail(mail_conn,'a_b@aaa-bbb.xx.yy');

utl_smtp.rcpt(mail_conn,'a_b@aaa-bbb.xx.yy');
mesg:= 'text';
utl_smtp.data(mail_conn,mesg);
utl_smtp.quit(mail_conn);
END;

ORA-29279: SMTP permanent error: 501 strangeness between : and <
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at line 8

this error occurs at the .mail call

What is it trying to tell me with between : and <

When I telnet this IP it responds initially but that's it

The mail server is notes/domin

any help is appreciated.


Tom Kyte
April 14, 2003 - 4:57 pm UTC

Well, thats not a database error -- that is the SMTP server being finicky.

RFC 821 states that all envelope information should be enclosed in angle brackets.

try putting <> about your email addresses.

smtp

Ray, April 17, 2003 - 12:47 pm UTC

yup that did it thanks

j., June 28, 2003 - 5:25 am UTC

hi tom, everything works fine so far.

now I wonder if there is any way to check whether given email addresses were valid.

can the database get notified if mail delivery fails?

Tom Kyte
June 28, 2003 - 9:19 am UTC

the email will bounce back and you can use the other parts of the javamail api to "read" mail as easily as "send mail"

so, instead of using utl_smtp, you can load up the java mail api and use it to do lots more.

How can we provide username and password for smtp auth

Monish, June 15, 2004 - 9:38 pm UTC

Hi Tom,

We are using SMTP Authentication. Is there a way I could provide username and password to the smtp server via pl/sql.

SQL> execute send_mail('support@xyz.com','abc@jfk.com','this is a test');
BEGIN send_mail('support@xyz.com','abc@jfk.com','this is a test'); END;

*
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 Authentication Required
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 240
ORA-06512: at "SCOTT.SEND_MAIL", line 11
ORA-06512: at line 1

Thanks 

Tom Kyte
June 16, 2004 - 12:13 pm UTC

you can write directly to the SMTP server and do the authorization, yes.

just telnet to your SMTP server (telnet hostname 25) and type HELP

> telnet aria 25
Trying 138.2.5.51...
Connected to aria.
Escape character is '^]'.
220 aria-lnx.us.oracle.com ESMTP Sendmail 8.11.6/8.11.6; Wed, 16 Jun 2004 12:02:18 -0400
HELP
214-2.0.0 This is sendmail version 8.11.6
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 AUTH
214-2.0.0 STARTTLS
214-2.0.0 For more info use "HELP <topic>".
214-2.0.0 To report bugs in the implementation send email to
214-2.0.0 sendmail-bugs@sendmail.org.
214-2.0.0 For local information send email to Postmaster at your site.
214 2.0.0 End of HELP info


You'll see a list of commands it supports. from there, you'll have to figure out the protocol your server is using to authenticate (should be able to google that up, some rfc somewhere)....

then you can use utl_smtp to write that information to the SMTP server.

it is very handy

Mehmood, January 19, 2005 - 2:06 am UTC

Dear Tom:

It is very handy to get emails from Oralce server. I have used the same procedure which you have given, I do get the emails from Oracle server having used the UTL_SMTP, but unfortunately I dont get any message in those emails. Your response will be highly appreicated.

Tom Kyte
January 19, 2005 - 10:36 am UTC

it has always worked for me.


ctl-f on this page for telnet. do it by hand. take the database out of the loop. All we are doing is exactly what you can do in telnet.

utl_smtp.data

Ratnamachary, April 20, 2005 - 9:57 am UTC

Tom,
I've problem in sending text more than 2000 charectors using utl.smtp.data function.
Is there any limitation for this?
I'm getting Numeric or Value error.
The text which I want to send I'm building in PL/SQL procedure and passing as input parameter for sendmail procedure.
I'm using Oracle version 8.1.7.

Please advise.

Thanks
-Chary

Tom Kyte
April 20, 2005 - 8:52 pm UTC

give example.

Error 530 - Authentication required

Manju P, September 20, 2005 - 5:22 am UTC

Hi,

I am trying to send a mail to myself. I am getting the same error. tried almost everything mentioned here.
ERROR at line 1:
ORA-20002: 530 Authentication required ORA-06512: at "SYS.UTL_SMTP", line 86 ORA-06512: at "SYS.UTL_SMTP", line 204
ORA-06512: at "CM_UAT.SEND_MAIL", line 10 ORA-06512: at line 2

sometimes i am getting error 421 - Service not available error.

latest is plsql statement executed successfully. BUT I STILL HAVEN'T RECEIVED THE MAIL

Tom Kyte
September 20, 2005 - 10:12 am UTC

that is saying you need to supply a username and password to use your SMTP server - you need to either

ask you admins for an smtp server that doesn't need that (likely you don't have one if they use one with authentication)

or

read a bit about the smtp protocol and see how to send the required authentication tokens.

Very helpful

Debra Miller, October 19, 2006 - 8:28 am UTC

I am printing this out to show my email admin because I am getting the ORA-29279 error, although a test from me to me works fine (puzzling).

Thanks!

Getting Headers too large error

naveen, March 16, 2007 - 9:35 am UTC

Hi Tom,
Greetings!

When use utl_smtp to send emails from oracle (9i) i get the below error message. Is there any limitation for the body of the email.

*
ERROR at line 1:
ORA-20009: ORA-20001: Error in Code for SMTP transaction: ORA-29279: SMTP
permanent error: 552 5.6.0 Headers too large (32768 max),l_step: Send the email
to the group if record(s) exists l_count: 341
ORA-06512: at "BAFWEB.QS_RECON", line 238
ORA-06512: at line 1


Regards,
Naveen.
Tom Kyte
March 17, 2007 - 3:52 pm UTC

it is saying headers too large, why do you think body is to blame?

Getting permanent_error while executing the code for the second time.

Babu R, July 26, 2007 - 3:42 pm UTC

I have the exception code defined in my procedure as mentioned above. While calling / executing the code from the application server, I'm getting the "parameters scanning TO failed to send mail" error. However, I get this only in the second time.

Any suggestions?

Thanks
Babu R

Mohammed Anwar, August 17, 2007 - 10:30 pm UTC

Hi Tom,

when i am sendig a mail through below Pl/Sql. it promt the below error :-
DECLARE
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 authentication required - for help go to
http://help.yahoo.com/help/us/mail/pop/pop-11.html
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at line 9

below is Code:-

DECLARE
l_mailhost VARCHAR2(64) := 'smtp.mail.yahoo.co.in';
l_from VARCHAR2(64) := 'mdanwar_23@yahoo.co.in';
l_to VARCHAR2(64) := 'mdanwar_23@yahoo.co.in';
l_mail_conn UTL_SMTP.connection;
BEGIN
l_mail_conn := UTL_SMTP.open_connection(l_mailhost, 25);
UTL_SMTP.helo(l_mail_conn, l_mailhost);
UTL_SMTP.mail(l_mail_conn, l_from);
UTL_SMTP.rcpt(l_mail_conn, l_to);
UTL_SMTP.data(l_mail_conn, 'Single string message.' || Chr(13));
UTL_SMTP.quit(l_mail_conn);
END;
/

Tom Kyte
August 22, 2007 - 10:07 am UTC

well, I'd goto http://help.yahoo.com/help/us/mail/pop/pop-11.html

and read that authorization is necessary and then read up on the smtp protocol so I would know how to send the USER and PASSWORD over to be successful here.


http://www.networksorcery.com/enp/rfc/rfc2554.txt

describes the protocol.

can i run banners in oracle?if yes how

pavan, April 08, 2008 - 5:31 am UTC

can i run banners i.e., rotation of text in oracle forms.If yes what is the query. ?
Tom Kyte
April 09, 2008 - 2:04 pm UTC

no idea what a "banner" is.

UTML_SMTP with Secured Servers

Cristobal Espinoza, April 24, 2008 - 2:37 pm UTC

Hi Tom!

I'm working with PLSQL Oracle 10g, I modified this procedure called send_mail:

PROCEDURE send_mail
( p_send_user_mail IN VARCHAR2,
p_receive_mails IN VARCHAR2,
p_subject IN VARCHAR2,
p_text IN VARCHAR2,
p_smtp_server IN VARCHAR2,
p_estatus OUT NUMBER)
is
connection UTL_SMTP.CONNECTION;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg VARCHAR2( 32767 );
BEGIN
connection := UTL_SMTP.OPEN_CONNECTION(p_smtp_server,25);
UTL_SMTP.EHLO(connection, p_smtp_server);
UTL_SMTP.COMMAND(connection,'AUTH LOGIN');
UTL_SMTP.COMMAND(connection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(p_send_user_mail))));
UTL_SMTP.COMMAND(connection,utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw('adantec'))));
mesg := 'Date: '||TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' )|| crlf ||
'From:'|| p_send_user_mail || crlf ||
'Subject: ' || p_subject || crlf ||
'To: '|| p_receive_mails || crlf ||
'' || crlf ||p_text||'';
UTL_SMTP.MAIL(connection, p_send_user_mail);
UTL_SMTP.RCPT(connection, p_receive_mails);
UTL_SMTP.OPEN_DATA(connection);
UTL_SMTP.WRITE_DATA(connection, 'MIME-Version: 1.0' || crlf || 'X-Priority: 1' || crlf || 'Content-type: text/html' || crlf || mesg);
UTL_SMTP.CLOSE_DATA(connection);
UTL_SMTP.QUIT(connection);

I got the following error. What should i do to run this.
ORA-29279: SMTP permanent error: 501 Syntax error in parameters or arguments to MAIL command
Tom Kyte
April 28, 2008 - 12:24 pm UTC

ORA-29279: SMTP permanent error: 501 Syntax error in parameters or arguments to
MAIL command

well, you are passing p_send_user_mail to the MAIL command, I would suspect you did it wrong. That is, the inputs you send to this routine are wrong, but you don't show us the INVOCATION, so we have no idea what is in the variable when it fails

ORA-29279: SMTP permanent error:

Suraj, July 16, 2008 - 6:25 am UTC

Hi Tom,
I want to send mail through Oracle but i am getting below error
ORA-29279: SMTP permanent error: 554 Mail from Thermax_Systems@thermaxindia.com rejected for policy reasons.
Tom Kyte
July 16, 2008 - 10:13 am UTC

uh huh, did you see the error message?

google: smtp 554 policy

your network people have a policy in place that says "no, this machine cannot connect to me and send mail"

Please talk with the people that do your email, configure your servers - tell them what your needs are and they can say whether or not you will be allowed to do it.

Open relay for oracle database server

Bhavesh, August 14, 2008 - 8:34 am UTC

Hi Tom,
I read in one of your comments that you do not have a database server that requires authentication for sending emails. I just wanted to know if allowing open relay in our mail server setup for oracle database server has any security issues? Is it a common practice to allow non authenticated access to oracle database servers so that our applications can send emails to outside world?

Currently I can send emails internally within my organization using utl_smtp. But as soon as I try sending email to outside address, it gives me following error.

550 5.7.1 Unable to relay for <email>@gmail.com

Thanks,
Bhavesh


Tom Kyte
August 18, 2008 - 10:01 am UTC

My machine is in a DMZ.

My machine is registered inside of Oracle.

My machine is allowed to contact our SMTP server and send outbound emails, they set up a rule to let me do that.

So, my server is permitted to use their SMTP service to relay emails to the outside world. This is safe, my machine is secured - the machine was granted the privilege to send email.

SMTP Service Extension for Authentication

Jose, February 17, 2009 - 6:19 am UTC

I have seen the docs and as far as i can see the utl_smtp package is not ready for using the SMTP Service Extension for Authentication. Is it so?
Are there any plans to implement this extension?
Thanks.

Tom Kyte
February 17, 2009 - 8:55 am UTC

why isn't it? it just lets you write commands to an smtp server, it can do whatever you want.

thanks

JOSE, February 17, 2009 - 12:21 pm UTC

Yes i think you mean to use the UTL_SMTP.COMMAND_REPLIES

Oracle send email

Marcel, June 02, 2010 - 1:50 pm UTC

Hi ,

I am trying to send emails using oracle and I am getting this error:
ORA-29279: erro permanente de SMTP: 550 not local host uol.com.br, not a
gateway

Thanks.
Tom Kyte
June 08, 2010 - 10:00 am UTC

You'd need to have your system administrator give you the information regarding a value SMTP host in your network.

Maybe you can glean that from your email client if you wanted...

Sending email

Aysha, October 12, 2010 - 10:40 am UTC

I create the procedure wich was compiled without error.
When executing the procedure, I do get any error, and the email was not sent...

Any help please, it's very urgent.
Aysha
Tom Kyte
October 12, 2010 - 10:54 am UTC

not knowing what procedure you created... I cannot respond. I'm hoping (fingers crossed) you didn't put a when others error handler in there or something - I cannot see the code you actually compiled like you can see mine.

How do you know it was not sent, just because it was not received by you does not mean it was not sent. Look at your smtp server logs - odds are if everything went OK - there was something wrong at the SMTP level.

smtp error

BMJ, July 07, 2011 - 1:53 am UTC

I am getting following error when I try to send a mail, pls let me know the reason for it

Failed To Send Mail ORA-29279: SMTP permanent error: 503 5.0.0 Polite people say HELO

Thanks
Tom Kyte
July 08, 2011 - 12:09 pm UTC

ctl-f for telnet on this page.

try it yourself and see what the response is, this is not an issue with Oracle, it is an issue with what your smtp server wants to hear. Try with telnet and see what happens - correct the interface from there...

SMTP permanent error: 535 5.7.8

neeru, April 19, 2012 - 4:38 am UTC

i am creating a procedure and i am passing correct username and password in it yet i am getting error while calling that procedure
ERROR at line 1:
ORA-29279: SMTP permanent error: 535 5.7.8 Error: authentication failed: authentication failure
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 158
ORA-06512: at "DSCH.SEND_MAIL", line 13
ORA-06512: at line 2
what would i do
Tom Kyte
April 19, 2012 - 5:53 am UTC

prove it, use telnet from the database server to put the commands right into your smtp server for us.

network access denied by access control list (ACL)

A reader, September 26, 2012 - 1:31 am UTC

In 11 G in am getting following error:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 127
ORA-06512: at "SYS.UTL_SMTP", line 150

In resolving the above issue when i try to add ACL by following query it never returns and hangs up

exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('MY.xml',
'Comments ..', 'SCOTT', TRUE, 'connect');

What can be problem on this installation as on one other instance the ACL is generated by this script. On both installation XML DB is available.