DNS resolve from Oracle Database 11g

For a a small home test datawarehouse I import all access.log data in a oracle database star shema. In 10g I used the following function to resolve the ip address to a hostname:

1.create or replace function resolve (ip varchar2) return varchar2 as

2.begin

3.return utl_inaddr.get_host_name(ip);

4.exception

5.when others then

6.return '-';

7.end;

8. 

In Oracle 10g this works out of the box. In 11g I get the following error:

SQL> select  utl_inaddr.get_host_name(‘127.0.0.1′) from dual;
select  utl_inaddr.get_host_name(‘127.0.0.1′) from dual
*
ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_INADDR”, line 4
ORA-06512: at “SYS.UTL_INADDR”, line 35
ORA-06512: at line 1

SQL>

The solution is to create an acl for the user broersa with the privilege resolve granted. After this we have to assign the acl to all hosts.

connect / as sysdba

exec dbms_network_acl_admin.create_acl(acl => ‘resolve.xml’,description => ‘resolve acl’, principal => ‘BROERSA’, is_grant => true, privilege => ‘resolve’)
exec dbms_network_acl_admin.assign_acl(acl => ‘resolve.xml’, host => ‘*’);
connect broersa/xxxxxx

SQL>  select  utl_inaddr.get_host_name(‘127.0.0.1′) from dual;

UTL_INADDR.GET_HOST_NAME(‘127.0.0.1′)
——————————————————————————–
localhost.bekijkhet.com

SQL>