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

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).

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.

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.

yup that did it thanks

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?

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 

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,
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

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

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!

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.

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

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;
/

can i run banners i.e., rotation of text in oracle forms.If yes what is the query. ?

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

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.

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

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.

Yes i think you mean to use the UTL_SMTP.COMMAND_REPLIES

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.

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

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

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

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.

Estou recebendo esse erro quando tento executar minha procedure envia email anexo, alguem poderia em auxiliar nessa duvida, pq ja tentei de tudo e nao deu certo

ORA-29277: invalid SMTP operation
ORA-06512: at "SYS.UTL_SMTP", line 43
ORA-06512: at "SYS.UTL_SMTP", line 320
ORA-06512: at "SIAS.SP_ENVIAR_EMAIL_COM_ANEXO", line 111
ORA-06512: at line 26