Re: translate an ip

If unknown_host (i.e. -29257) is thrown by SYS.utl_inaddr.get_host_name you may want to add a PRAGMA (see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/exceptioninit_pragma.htm#LNPLS01315) along the lines

   PRAGMA EXCEPTION_INIT(unknown_host,unknown_host_errcode);

so that it will be caught by your exception handler.

Or alternatively add an exception handler (see http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_inaddr.htm#sthref14805) such as

   WHEN sys.utl_inaddr.unknown_host THEN      RETURN(input_val);

to the code.

Hope that helps,
Michael S.

Quoting Andrew Kerber <andrew.kerber_at_gmail.com>:

> Hello experts,
>
> I am trying to build a function to translate an IP address into its name, or
> vice versa. I have a mixed bag of IP addresses and DNS names, all culled
> from AUD$ history tables. What I need is a consistent format, either IP or
> DNS name, so I can consolidate the information. I am trying to use
> utl_inaddr.get_host_name. It works, but if it encounters an IP that it
> can't translate, my SQL statement fails. The same for using
> utl_inaddr.get_host_address. What I have so far is below. It compiles, but
> it doesn't catch the error. What I need is something that will either
> translate the address, or else return the original value. I would
> appreciate any help you might have to offer.
>
> Create or replace function translate_address (input_val IN VARCHAR2) RETURN
> VARCHAR2
> is
> return_val varchar2(30);
> unknown_host_errcode CONSTANT PLS_INTEGER := -29257;
> unknown_host EXCEPTION; -- Unknown host
> BEGIN
> select SYS.utl_inaddr.get_host_name(input_val) into return_val from DUAL;
> if input_val is not null
> then
> RETURN (return_val);
> else
> raise unknown_host;
> end if;
> EXCEPTION
> WHEN unknown_host
> THEN
> RETURN (input_val);
> END;
> /
>
> Thanx,
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

--
http://www.freelists.org/webpage/oracle-l