Opening up email in Apex 4 on Oracle XE 11

Your ads will be inserted here by

Easy Plugin for AdSense.

Please go to the plugin admin page to
Paste your ad code OR
Suppress this ad slot.

Hi,

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>

<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
</EDIT>

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.

Leave a Reply