Good day all,

Let see a issue which appear after migration database from 10g to 11gR2

When executing DBMS_LDAP, UTL_INADDR (or) UTL_HTTP (or) any UTL packages after upgrading to 11gR1, fails with following error.

ORA-20222: ORA-24247: network access denied by access control list (ACL)

Cause of error is new security measure is introduced in Oracle 11g  for the following network-related PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL,  UTL_INADDR, DBMS_LDAP.By default, the ports are blocked.

We find Solution:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'http_service.xml',
description => 'HTTP ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'http_service.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'http_service.xml',
host => '*');
COMMIT;
END;

In our case user SCOTT has a privileges use http_service. If you want to open port for specific host on assign_acl part use direct IP instead of ‘*”