QUERYING A SERVER’S IP ADDRESS

 
REQUEST

 
SOLUTION

A hostname and IP address can be retrieved through UTL_INADDR package

LOCAL HOST

oracle@dbpilot.net: nslookup dbpilot.net
Server:         10.1.1.50
Address:        10.1.1.50#53

Name:   dbpilot.net
Address: 10.1.1.10

COL IP FOR A10
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS IP FROM DUAL;

IP
----------
10.1.1.10

COL HOSTNAME FOR A15
SELECT SYS.UTL_INADDR.GET_HOST_NAME HOSTNAME FROM DUAL;

HOSTNAME
---------------
dbpilot.net

A REMOTE HOST

oracle@dbpilot.net: nslookup another.host
Server:         10.1.1.50
Address:        10.1.1.50#53

Name:   another.host
Address: 10.1.1.20

COL IP FOR A10
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('another.host') IP FROM DUAL;

IP
----------
10.1.1.20

COL HOSTNAME FOR A15
SELECT SYS.UTL_INADDR.GET_HOST_NAME('10.1.1.20') HOSTNAME FROM DUAL;

HOSTNAME
---------------
another.host

NON EXISTING HOST

oracle@dbpilot.net: nslookup 1.1.1.1
Server:         10.1.1.50
Address:        10.1.1.50#53

** server can't find 1.1.1.1.in-addr.arpa.: NXDOMAIN

oracle@dbpilot.net: nslookup non.existing.host
Server:         10.1.1.50
Address:        10.1.1.50#53

** server can't find non.existing.host: NXDOMAIN

SQL> COL IP FOR A10
SQL> SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('non.existing.host') IP FROM DUAL;
SELECT SYS.UTL_INADDR.GET_HOST_ADDRESS('non.existing.host') IP FROM DUAL
       *
ERROR at line 1:
ORA-29257: host non.existing.host unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

SQL> COL HOSTNAME FOR A30
SQL> SELECT SYS.UTL_INADDR.GET_HOST_NAME('1.1.1.1') HOSTNAME FROM DUAL;
SELECT SYS.UTL_INADDR.GET_HOST_NAME('1.1.1.1') HOSTNAME FROM DUAL
       *
ERROR at line 1:
ORA-29257: host 1.1.1.1 unknown
ORA-06512: at "SYS.UTL_INADDR", line 4
ORA-06512: at "SYS.UTL_INADDR", line 35
ORA-06512: at line 1