ORA-24247 Trying To Send Email Using UTL_SMTP from 11g

After upgrading a database to 11gR trying to send emails using UTL_SMTP fail with

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 267

ORA-06512: at "SYS.UTL_SMTP", line 161

ORA-06512: at "SYS.UTL_SMTP", line 197

ORA-06512: at line 9

Starting with Oracle 11g "fine-grained access" was implemented to limit usage of packages like UTL_SMTP, UTL_HTTP connecting over the network to other services like mail server etc.

By default, the ports are blocked and ORA-24247 is raised to signal this.

To control the ACL, Package DBMS_NETWORK_ACL_ADMIN can be used to create ACL and grant access to particular user.

BEGIN

  -- Only uncomment the following line if ACL "network_services.xml" has already been created

  --DBMS_NETWORK_ACL_ADMIN.DROP_ACL('network_services.xml');

  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(

    acl => 'network_services.xml',

    description => 'FTP ACL',

    principal => 'USER_NAME',

    is_grant => true,

    privilege => 'connect');

  DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(

    acl => 'network_services.xml',

    principal => 'USER_NAME',

    is_grant => true,

    privilege => 'resolve');

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(

    acl => 'network_services.xml',

    host => '*');

  COMMIT;

END;

Bingo, it works now!