I am having trouble configuring my system to send e-mail from Apex. The machine is a virtual machine running on Amazon's AWS with the fixed name merlin.mavericksolutions.net. Running Apex 4.2.3.00.08 with Oracle 11g R2 on Windows 2008 Server.
Before I try to configure it through APEX, I know I have to enable the network services in the database. So, I'm attempting to even get it working "natively" in the database, without having Apex even involved yet. I am attempting to piece together various sets of incomplete instructions from multiple blogs, with no success. Although I'm not technically trying anything in APEX yet, I figured that the experts over here in the APEX forum must have the expertise with this exact thing.
Exact error message: 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 "MERLIN.SEND_MAIL", line 9
ORA-06512: at line 2
Here are the steps that I used to create the ACL, beginning with the installation of utl_mail and utl_smtp:
======================================================================================
Conn sys as sysdba
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
GRANT execute on utl_mail to public;
Grant execute on utl_smtp to public;
alter system set smtp_out_server = 'smtp.merlin.mavericksolutions.net:25' scope=both;
shutdown immediate
startup
======================================================================================
select host, lower_port, upper_port, acl
from dba_network_acls
======================================================================================
begin
dbms_network_acl_admin.create_acl (
acl => 'utl\_smtp.xml',
description => 'Allow mail to be sent',
principal => 'MERLIN',
is\_grant => TRUE,
privilege => 'connect'
);
commit;
end;
begin
dbms_network_acl_admin.assign_acl (
acl => 'utl\_smtp.xml',
host => 'merlin.mavericksolutions.net',
lower\_port => 25,
upper\_port => 25
);
COMMIT;
END;
begin
dbms_network_acl_admin.add_privilege (
acl => 'utl_smtp.xml',
principal => 'MERLIN',
is_grant => TRUE,
privilege => 'resolve'
);
commit;
end;
======================================================================================
select host, lower_port, upper_port, acl
from dba_network_acls
======================================================================================
CREATE OR REPLACE PROCEDURE send_mail (p_to IN VARCHAR2,
p\_from 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.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
UTL_SMTP.quit(l_mail_conn);
END;
======================================================================================
conn merlin
BEGIN
send_mail(p_to => 'mark@csthompson.com',
p\_from => ['mark.thompson@mavericksolutions.net](mailto:'mark.thompson@perunapartners.net)',
p\_message => 'This is a test message.',
p\_smtp\_host => 'merlin.mavericksolutions.net',
p\_smtp\_port => 25);
END;
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 "MERLIN.SEND_MAIL", line 9
ORA-06512: at line 2
======================================================================================
What else I have tried:
- Setting up an ACL for utl_mail instead of utl_smtp: same error
- Setting up an ACL for utl_mail in addition to utl_smtp: same error
-
However, when I do that, I am using the same port (25) and when I do that I only
see the one additional entry in the dba_network_acls.
- Using localhost instead of the real machine name
======================================================================================
Am I even close to having the right answer? I have not yet sacrificed a young goat by the light of a full moon at midnight, but that's next.
Message was edited by: Mark T. Corrected a typo in the original post.