Print Friendly, PDF & Email

 
REQUEST

I want to gather a hostname and/or IP address of the current host or a remote one.

 
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

 
 

Version  : 16:47 19.01.2018
Database : Oracle database 10g – 12cR2