[sourcecode language="sql"]
create or replace function resolve (ip varchar2) return varchar2 as
begin
return utl_inaddr.get_host_name(ip);
exception
when others then
return '-';
end;
[/sourcecode]
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>