Saturday, August 25, 2007

ORA-29257: host unknown

Although there is very little mystery around this error, returned by packaged function UTL_INADDR.GET_HOST_NAME when an IP address without a matching domain name is supplied, it may be useful to know, as John Scott warned me during a short talk we had on this subject, that it could be a very bad idea to run a query on a table containing thousands of rows of IP addresses, because this function has the peculiarity of waiting up to 60 seconds before raising the timeout error.
As you can easily imagine, on a table with 10,000 or 100,000 addresses, assuming 10% without a corresponding domain, you may end up waiting for 6,000 or 60,000 seconds before the query completes!

You can easily see this yourself, on a smaller scale, running the following query:

select utl_inaddr.get_host_name('') from dual

ORA-29257: host unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1

If you really really need to resolve the host name of an incoming ip address, a typical requirement for a web counter for instance, it could be much more performing to create a materialized view where you cache the result of the call to UTL_INADDR.GET_HOST_NAME (this was another smart suggestion of John).

Last but not least, if you are wondering about how you can get the ip address of a remote user, an Oracle Application Express end user for instance, Oracle comes with function GET_CGI_ENV in package OWA_UTIL, a handy utility that returns many useful values given an input string, where, among others there is 'REMOTE_ADDR' (see the documentation for the list of acceptable cgi variables):
select owa_util.get_cgi_env('REMOTE_ADDR') as ipaddr from dual;
However if the db server is behind a firewall or proxy, chances are that the address of the firewall is returned, not the remote ip, but this is another story altogether.

See message translations for ORA-29257 and search additional resources.

ORA-29257: host sconosciuto
ORA-29257: host desconocido
ORA-29257: host desconegut
ORA-29257: hôte inconnu
ORA-29257: Host unbekannt
ORA-29257: κεντρικός υπολογιστής άγνωστος
ORA-29257: vært ukendt
ORA-29257: värddatorn okänd
ORA-29257: verten er ukjent
ORA-29257: pääkone on tuntematon
ORA-29257: A(z) gazda ismeretlen.
ORA-29257: gazda nu este cunoscută
ORA-29257: Host is onbekend.
ORA-29257: host desconhecido
ORA-29257: o host é desconhecido
ORA-29257: хост неизвестен
ORA-29257: neznámý hostitel
ORA-29257: hostiteľ neznámy
ORA-29257: host jest nieznany
ORA-29257: ana bilgisayarı tanınmıyor
ORA-29257: host unknown

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles