I recently installed a new server (mainly by using the installation guide you can find in the menu).
I think this needs an extra step, since I wasn’t able to send out emails from that system using Apex.
In the email-queue it kept saying: ORA-24247: network access denied by access control list (ACL)
A quick “Google”, I ended up here : http://blogs.oracle.com/pankaj/entry/how_to_get_mail_working_with_a
So I needed to execute something (read the blog), I did that as SYS:
<EDIT 12 sep 2011>
Please adjust the name of your apex schema in the code below, by adding / removing the remarks for the t_apex_user variable within the declare section of the code.
<EDIT 29 mar 2013>
I altered the script into a more generic thing. It now takes a few variables:
t_apex_user contains the user for which you need to grant.
t_hostname is the host invloved (this can be ‘*’)
t_new_acl_name holds the name of the ACL in case a new ACL must be created
t_new_acl_descr is the description for this ACL
This makes this into a more generic ACL script, not only for opening up email for the apex user
declare t_acl_path varchar2(4000); t_acl_id raw(16); -- t_apex_user varchar2(30) := 'APEX_030200'; -- t_apex_user varchar2(30) := 'APEX_040000'; t_apex_user varchar2(30) := 'SPELEN'; t_hostname varchar2(2000) := 'accounts.google.com'; t_new_acl_name varchar2(2000); t_new_acl_descr varchar2(2000) := 'ACL that lets power users to connect to maps.google.com'; begin -- Look for the ACL currently assigned to '*' and give t_apex_user -- the "connect" privilege if t_apex_user does not have the privilege yet. if t_hostname = '*' then t_new_acl_name := 'power_users.xml'; else t_new_acl_name := t_hostname || '.xml'; end if; select acl into t_acl_path from dba_network_acls where host = t_hostname and lower_port is null and upper_port is null; -- Before checking the privilege, make sure that the ACL is valid -- (for example, does not contain stale references to dropped users). -- If it does, the following exception will be raised: -- -- ORA-44416: Invalid ACL: Unresolved principal t_apex_user -- ORA-06512: at "XDB.DBMS_XDBZ", line ... -- select sys_op_r2o(extractvalue(p.res, '/Resource/XMLRef')) into t_acl_id from xdb.xdb$acl a , path_view p where extractvalue(p.res, '/Resource/XMLRef') = ref(a) and equals_path(p.res, t_acl_path) = 1; dbms_xdbz.validateacl(t_acl_id); if dbms_network_acl_admin.check_privilege(t_acl_path, t_apex_user, 'connect') is null then dbms_network_acl_admin.add_privilege(t_acl_path, t_apex_user, true, 'connect'); end if; commit; exception -- when no acl has been assigned to '*'. when no_data_found then dbms_network_acl_admin.create_acl(t_new_acl_name, t_new_acl_descr, t_apex_user, TRUE, 'connect'); dbms_network_acl_admin.assign_acl(t_new_acl_name, t_hostname); end;
I had to replace the “FLOWS_030000” by “APEX_040000” in Pankaj’s script. After this everything worked like a charm.