How to Look Up Host Names with UTL_INADDR

UTL_INADDR resolves host names to IP addresses and IP addresses to host names, from inside the database. It answers questions such as which address a web service resolves to from the database server, or what the name of the server itself is.

Code for This Guide

The main examples are in the examples/pkg-files-network folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

utl_inaddr.get_host_address([host])    -- null: the database server itself
utl_inaddr.get_host_name([ip_address])

Lookups need an access control entry with the resolve privilege for the host.

Look Up Names and Addresses

Example:

select utl_inaddr.get_host_address('host.docker.internal') as test_server,
       utl_inaddr.get_host_address('localhost')            as localhost,
       utl_inaddr.get_host_name('127.0.0.1')               as name_of_127;

Output:

TEST_SERVER       LOCALHOST    NAME_OF_127
_________________ ____________ ______________
192.168.65.254    127.0.0.1    localhost

host.docker.internal, the name the database container uses for the machine it runs on, resolves to 192.168.65.254. localhost is 127.0.0.1, and the reverse lookup of 127.0.0.1 gives localhost. The addresses depend on your network.

A Host That Does Not Exist

Example:

select utl_inaddr.get_host_address('no-such-host.invalid') from dual;

Output:

Error starting at line : 1
In command -
select utl_inaddr.get_host_address('no-such-host.invalid') from dual
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-29257: host no-such-host.invalid unknown
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

A name that cannot be resolved raises ORA-29257, which code can catch to report a bad host name before trying to connect.

Things to Know

• Names are resolved by the database server, with its own DNS settings, which may differ from your computer's.
• Without a resolve entry for the host, the call fails with ORA-24247.
• SYS_CONTEXT('USERENV', 'SERVER_HOST') gives the database server's own host name without a lookup.

Related Guides

• How to Allow Network Access with DBMS_NETWORK_ACL_ADMIN
• Oracle SYS_CONTEXT Function

Conclusion

UTL_INADDR.GET_HOST_ADDRESS and GET_HOST_NAME resolve names and addresses as the database server sees them. Grant the resolve privilege, and catch ORA-29257 for names that do not exist.