Troubleshoot: ORA-24247 when trying to send an email

July 20, 2025 No Comments

It may sound made up, but there are still installations running Oracle 10.2, and it was precisely in one of these, just upgraded to 19c, that a problem developed for which they wanted my assistance: a rarely used application, which sends an alert email as part of its steps, quit working.

Fortunately, the solution is quite simple, as we will see below. Keep reading.

The problem

Until Oracle Database 11gR1, you only needed to grant execute permissions to use the UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP, and UTL_HTTP packages; however, now you must utilize a specific configuration known as an Access Control List (ACL).

Without this configuration, all interactions with external network services will be blocked, resulting in the error ORA-24247: network access denied by access control list (ACL).

Copy

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Warning!',
    message    => 'Oracle 23ai on-prem is still not released');
END;
/

BEGIN
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 432
ORA-06512: at "SYS.UTL_TCP", line 19
ORA-06512: at "SYS.UTL_TCP", line 295
ORA-06512: at "SYS.UTL_SMTP", line 164
ORA-06512: at "SYS.UTL_SMTP", line 201
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 679
ORA-06512: at line 2

The solution

To obtain full permissions to use the UTL_INADDR, UTL_SMTP, UTL_MAIL, UTL_TCP, and UTL_HTTP packages, you must now additionally grant permission through the use of the DBMS_NETWORK_ACL_ADMIN package, as follows:

Copy

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  )
  ); 
END;
/

Where:

In host, we specify the name or IP address of the server we need to access.
In lower_port, we indicate the TCP port where our requests will be handled.
In ace, we specify the privilege to be granted, who gets it, and the type of user.
1. In privilege_list, we might specify connect, but that would not only allow access to UTL_MAIL and UTL_SMTP, but also to UTL_TCP and UTL_HTTP, so it is recommended to specify smtp for better control.
2. In principal_name, we specify the database user, which in our case will be the ADMIN schema.
3. In principal_type, we will always indicate ptype_db, since we are configuring a database user.

Lastly, we make sure that the code is functioning normally again, a sign that the issue has been fixed.

Copy

BEGIN
  UTL_MAIL.send(
    sender     => 'admin@acme.com',
    recipients => 'larry@acme.com',
    cc         => 'scott@acme.com',
    bcc        => 'tom@acme.com',
    subject    => 'Advertencia!',
    message    => 'Oracle 23ai on-prem sigue sin liberarse');
END;
/

PL/SQL procedure successfully completed.

Additional tasks

You can determine which users need extra configuration (ACL) by using this query.:

Copy

SELECT DISTINCT owner, referenced_name 
  FROM dba_dependencies
 WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP')
   AND owner NOT IN ('SYS','PUBLIC','GSMADMIN_INTERNAL','ORACLE_OCM')
 ORDER BY owner, referenced_name;
 
OWNER    REFERENCED_NAME
-------- ---------------
ADMIN    UTL_HTTP
ADMIN    UTL_MAIL
SCOTT    UTL_HTTP
SCOTT    UTL_SMTP
SCOTT    UTL_TCP

You can check the privileges already granted by running the following query:

Copy

SELECT host,
       lower_port,
       upper_port,
       principal,
       principal_type,
       privilege
  FROM dba_host_aces
 WHERE host = 'smtp.acme.com';
 
HOST          LOWER_PORT UPPER_PORT PRINCIPAL PRINCIPAL_TYPE PRIVILEGE
------------- ---------- ---------- --------- -------------- ---------
smtp.acme.com         25         25 ADMIN     DATABASE       SMTP

The following command can be used to revoke a privilege that has already been granted:

Copy

BEGIN
  DBMS_NETWORK_ACL_ADMIN.remove_host_ace (
    host       => 'smtp.acme.com', 
    lower_port => 25,
    ace        => xs$ace_type( 
                    privilege_list => xs$name_list( 'smtp' ),
                    principal_name => 'ADMIN',
                    principal_type => xs_acl.ptype_db
                  ),    
    remove_empty_acl => TRUE); 
END;
/

This is just one of many cases where ACL applies. To explore more situations, check out this article by Tim Hall. However, keep in mind that it focuses on the procedure used with Oracle 11gR1 and 11gR2, since the procedure has changed since Oracle 12cR1, as detailed in this updated article.

In addition to what has already been said, I recommend that you read the notes below:

958129.1	How To Set Network ACLs in Oracle To Access Packages UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, UTL_INADDR
1209644.1	ORA-24247 Using UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL With 11gR1 Or Later