Symptoms:
You try to make external connections from the Oracle Database and receive erros like
ORA-24247: network access denied by access control list (ACL)
SQL> select utl_http.request('http://www.yourwebsite.com') from dual;
select utl_http.request('http://www.yourwebsite.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1
Cause:
The reason is that your database does not have permission to make external connections (through the internet - url or public IP).
So you have to configure explicity what hostname, website or public IP is allowed. Morover, you can be more specific on what port to open and what privilege to do so.
Solution:
First of all, you can make sure if there is any privilege for any database user. Empty resultset (that is default) means no privilege, you have to configure.
To ADD permission:
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '*',
-- lower_port => 80,
-- upper_port => 80,
ace => xs$ace_type(privilege_list => xs$name_list('connect','resolve','http'), -- HERE you can specify the privileges, the three of there are more than enough
principal_name => '<YOUR_DATABASE_USER>', -- HERE you specify the database user that will make the external calls.
principal_type => xs_acl.ptype_db));
END;
/
COMMIT;
To REMOVE an existent privilege, in case you need to remove.
begin
dbms_network_acl_admin.remove_host_ace(
host => '*',
ace => xs$ace_type(privilege_list => xs$name_list('http'),
principal_name => '<YOUR_DATABASE_USER>',
principal_type => xs_acl.ptype_db));
end;
/
Once added or removed, execute the query above to make sure what permissions and users are configured.
Hope that helps.
