ORA-29279: SMTP permanent error: 530 5.7.0 authentication required

Question

Please help me, I'm trying to send mail from Oracle database 11g and while sending the mail from database, I'm getting the following error.

enter image description here

  CREATE OR REPLACE PROCEDURE send_mail_deepak_test (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);

  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/

Answer 1

The message is very clear. Authenticate:

base64username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('a@b.com')));  
base64password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('1234')));

con:= UTL_SMTP.open_connection('myhost', 25);
UTL_SMTP.ehlo(con, smtpHost);
UTL_SMTP.command(con, 'AUTH', 'LOGIN');
UTL_SMTP.command(con, base64username );
UTL_SMTP.command(con, base64password );

Another solution is - like @FDavidoc mentioned - to ask your Admin to lower the security for your machine and allow sending mails without Auth. This way you don't have to worry about passwords.

Answer 2

Maybe your p_to variable consists of more than one email recipient. I have got this error for this cause. if you want to send multiple recipients: You should look at this answer.