Thursday, November 12, 2009

DNS resolve from Oracle Database 11g

Thursday, November 12, 2009 Posted by Andre Broers 8 comments
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>

8 comments:

  1. Coach Outlet http://www.coachoutletfactory-stores.us.com

    Kate Spade Outlet http://www.katespadefactoryoutlet.in.net

    ED Hardy Outlet http://www.edhardypopsale.com

    Coach Outlet Store Online http://www.coachstoreonline.in.net

    Kate Spade Outlet http://www.katespadeoutletstore.in.net

    Cheap Jordans http://www.cheapjordansretro.us.com

    Coach Purses http://www.coachpurses.eu.com

    Coach Outlet http://www.coachoutlet2016.us.com

    Tory Burch Shoes http://www.toryburchoutlets.us.com

    Longchamp Outlet http://www.longchampoutlet.in.net

    Michael Kors Handbags http://www.innnov8.com

    Coach Outlet http://www.innnovate.com

    Red Bottom Shoes http://www.redbottomshoes.us.org

    Moncler Jackets http://www.monclerdownjackets.us.com

    Kate Spade Outlet http://www.katespadeoutletstore.us.com

    Moncler Outlet http://www.moncleroutletonline.in.net

    Coach Factory http://www.maganates.com

    Coach Factory Outlet http://www.coachfactorysoutlet.in.net

    Kate Spade Outlet http://www.katespadesoutlet.us.com

    Ray Ban Outlet http://www.raybanoutletstore.in.net

    Christian Louboutin Shoes http://www.christianlouboutinshoes2016.in.net

    North Face Outlet http://www.northfaceoutlets.us.org

    Coach Factory Outlet http://www.coachoutletstore.name

    Kate Spade Outlet http://www.katespadeoutletonline.name

    Coach Outlet http://www.coach-outlets.us.org

    Nike Roshe Run http://www.tanie-nikerosherun.pl

    Buty Nike Air Max http://www.airmaxsprzedazdla.pl

    Nike Air Max http://www.nikeairmax.us.org

    Red Bottom Shoes http://www.redbottomshoe.in.net

    Christian Louboutin Outlet http://www.christianlouboutinoutlet.us.org

    Balenciaga Outlet http://www.balenciaga-outlet.us.com

    Balenciaga Handbags http://www.balenciaga-handbags.us.com

    Kate Spade Outlet http://www.katespadefactoryoutlet.in.net

    Toms Shoes http://www.tomshoesoutlet.us.com

    Hermes Belt http://www.hermes-belts.us.com

    Prada Outlet http://www.pradafactoryoutlet.com

    Louis Vuitton Outlet http://www.louisvuittonsoutlet.us.com

    North Face Outlet Store http://www.northfaceoutletstore.in.net

    North Face Jackets http://www.north-face.in.net

    North Face http://www.north-faceoutlets.us.com

    Louis Vuitton Outlet http://www.louisvuitton-outlets.us.org

    Timberland Boots http://www.timberland-outlets.us.com

    Cheap Timberland Boots http://www.timberlandoutlet.us.com

    Tory Burch Outlet http://www.toryburch-outlets.us.com

    Ralph Lauren Outlet http://www.ralphlaurenstoreoutlet.us.com

    Gucci Shoes http://www.guccifactoryoutlet.name

    Gucci Outlet http://www.guccioutletinc.us.com

    North Face Jackets http://www.northfacefactoryoutlet.us.com

    Prada Outlet http://www.pradaoutlets.in.net

    Hollister Clothing http://www.hollister-clothing.us.com

    Ferragamo Shoes http://www.ferragamoshoes.us.org

    Tiffany Outlet http://www.tiffany-outlets.us.com

    Tiffany Outlet http://www.tiffanyco-outlet.us.com

    NFL Jerseys http://www.nfljerseys.name

    Toms Outlet http://www.outlettoms.us.com

    Cheap Jordans http://www.cheapjordans.eu.com

    North Face Outlet http://www.thenorthfaceoutlet.us.org

    Prada Handbags http://www.pradaoutlet.us.org

    Chan Luu http://www.chanluu.us.com

    Toms Outlet http://www.tomsoutletstores.us.com

    Kate Spade Outlet http://www.katespadeoutletofficial.us.com

    Beats By Dr Dre http://www.beatsbydrdre.us.org

    Coach Outlet http://www.coachstoreoutlet.us.org

    Christian Louboutin Shoes http://www.christianlouboutinshoe.eu.com

    Valentino Shoes http://www.valentinoshoes.eu.com

    Michael Kors Outlet http://www.michaelkorsoutlet.us.org

    Coach Factory Outlet http://www.coachfactoryoutlet.us.org

    Coach Outlet Online http://www.coachoutletonlinestore.us.org

    Coach Purses http://www.coachpurse.us.com

    ReplyDelete