Problem:

-- As Scott User
CREATE OR REPLACE PROCEDURE send_mail (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;
/


begin
  send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject   => 'TEST SEND MAIL',p_message   =>'test',p_smtp_host => '127.0.0.1');
 end;
 /
*
ERROR at line 1:
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
ORA-06512: at "SCOTT.SEND_MAIL", line 10
ORA-06512: at line 2


Solution:

This is because the SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

-- AS sys User
SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

no rows selected.

This is because the SCOTT does not have the privilege to access the mail/smtp server. So it must be added to the ACL.

Create and assign ACL.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'send_mail.xml',
    description  => 'Purpose of the acl is to send mail',
    principal    => 'SCOTT',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

   DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'send_mail.xml',
    host        => '127.0.0.1',
    lower_port  => 1,
    upper_port  => 9999);

  COMMIT;
END;
/
SELECT host, lower_port, upper_port, acl FROM dba_network_acls ;

HOST	LOWER_PORT	UPPER_PORT	ACL
127.0.0.1	1	9999	/sys/acls/send_mail.xml

 

-- Now try Again
-- As scott User
begin
  send_mail(p_to=> 'mdnazmulhuda@test.com',p_from=> 'oracle@test.com',p_subject   => 'TEST SEND MAIL',p_message   =>'test',p_smtp_host => '127.0.0.1');
 end;
 /
PL/SQL procedure successfully completed.