Thanks for the question regarding "About utl_smtp", version 8.1.7

You Asked (Jump to Tom's latest followup)

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('[e-mail]',
        '[e-mail]',
        '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. 

	Reviews
GOTO a page to Bookmark Review | Bottom | Top ThanksDecember 03, 2001 Reviewer: Balasubramanian from Singapore 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 Followup: 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: [e-mail] 250 [e-mail]... Sender ok rcpt to: [e-mail] 250 [e-mail]... 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top Relaying Denied fixesDecember 03, 2001 Reviewer: Andy Finkenstadt from Saint Charles MO 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). GOTO a page to Bookmark Review | Bottom | Top Ora-20001 :450 when executing procedure.March 31, 2003 Reviewer: Vivek Sharma from Bombay, India 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,'[e-mail]'); utl_smtp.rcpt(mail_conn,mail_id); utl_smtp.rcpt(mail_conn,'[e-mail]'); 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. Followup: 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: [e-mail] 250 2.1.0 [e-mail]... Sender ok RCPT TO: [e-mail] 250 2.1.5 [e-mail]... 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top utl_smtp strangeness 9.2April 14, 2003 Reviewer: Ray from Ottawa,On,ca 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,'[e-mail]'); utl_smtp.rcpt(mail_conn,'[e-mail]'); 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. Followup: 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top smtpApril 17, 2003 Reviewer: Ray from Ottawa,on,ca yup that did it thanks GOTO a page to Bookmark Review | Bottom | Top June 28, 2003 Reviewer: j. 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? Followup: 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top How can we provide username and password for smtp authJune 15, 2004 Reviewer: Monish from CO, US 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('[e-mail]','[e-mail]','this is a test'); BEGIN send_mail('[e-mail]','[e-mail]','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 Followup: 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 [e-mail]. 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top it is very handy January 19, 2005 Reviewer: Mehmood from Karachi, Pakistan 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. Followup: 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. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top utl_smtp.dataApril 20, 2005 Reviewer: Ratnamachary from IND 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 Followup: give example. <script language="JavaScript1.1" type="text/javascript"> document.getElementById("latestLink").style.display = "inline" </script> GOTO a page to Bookmark Review | Bottom | Top Error 530 - Authentication requiredSeptember 20, 2005 Reviewer: Manju P from IND 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 Followup: 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.