I am working on oracle 11g, UTL SMTP mail processing.
I have an oracle daily scheduler and if it fails, I need to send a mail notification to user.
I am using windows 7 system, and oracle 11g r2 and using microsoft exchange mail server.
Below are the steps i have followed,
connect / as sysdba;
grant execute on utl_mail to user1;
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME /rdbms/admin/prvtmail.plb
alter system set smtp_out_server = 'smtp.office365.com' scope=both;
--Create an access control list:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'utl_mail_company.xml',
description => 'Permissions to access company e-mail server.',
principal => 'USER1',
is_grant => TRUE,
privilege => 'connect');
COMMIT;
END;
/
--Add privileges to access control list (Adding users/roles to ACL):
BEGIN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
acl => 'utl_mail_company.xml',
principal => 'USER1',
is_grant => TRUE,
privilege => 'resolve');
COMMIT;
END;
/
-- Assign the list to the smtp ( mail server ):
-- Note Default port is 25!
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'utl_mail_company.xml',
host => 'smtp.office365.com',
lower_port => 25,
upper_port => NULL
);
COMMIT;
END;
/
--mail sending procedure:
create or replace PROCEDURE send_mails (p_to IN VARCHAR2,
p_from IN VARCHAR2,
p_message IN VARCHAR2,
p_smtp_host IN VARCHAR2,
p_smtp_port IN NUMBER DEFAULT 587)
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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
--execution:
BEGIN
send_mails(p_to => 'testdest@company.com',
p_from => 'test1@company.com',
p_message => 'This is a test message.',
p_smtp_host => 'smtp.office365.com');
END;
/
still i am getting below error:
Error report -
ORA-29279: SMTP permanent error: 530 5.7.57 SMTP; Client was not authenticated to send anonymous mail during MAIL FROM
ORA-06512: at "SYS.UTL_SMTP", line 29
ORA-06512: at "SYS.UTL_SMTP", line 110
ORA-06512: at "SYS.UTL_SMTP", line 233
ORA-06512: at "COHORT.SEND_MAILS", line 12
ORA-06512: at line 2
29279. 00000 - "SMTP permanent error: %s"
Am i missing any steps/process guys?
i am not able find further info and need help on this.