This short blog post will demonstrate the usage of the new DBMS_TNS package, which was first introduced with the release of Oracle 12.2.0.1 and can be used to resolve a TNS alias to its full connect descriptor.
Table of Contents
About the package
With the PL/SQL package DBMS_TNS, it is now possible to resolve a TNS alias – e.g used as target for a database link – to the connect descriptor defined in the tnsnames.ora file of the database server.
The first version of this package has only one function, RESOLVE_TNSNAME, which returns the connect descriptor as VARCHAR2 value.
FUNCTION RESOLVE_TNSNAME RETURNS VARCHAR2 Argument Name Type In/Out Default? ----------------- ------------ ------ -------- TNS_NAME VARCHAR2 IN
Usage
To resolve a TNS alias, just pass it to the function. You don’t have to worry about case sensitivity.
SQL> SELECT DBMS_TNS.RESOLVE_TNSNAME('L122EEC1_SITE1') FROM DUAL;
DBMS_TNS.RESOLVE_TNSNAME('L122EEC1_SITE1')
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DB12CR2-OL7-S1.goh.trivadis.local)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=L122EEC1_SITE1.goh.trivadis.com)(CID=(PROGRAM=oracle)(HOST=DB12CR2-OL7-S1.goh.trivadis.local)(USER=oracle))))
Remarks:
- If no entry was found or no tnsnames.ora exists or its content is corrupted, an empty string is returned
- If two entries with the same name exist in the tnsnames.ora, the last connect descriptor is returned
- If NAMES.DEFAULT_DOMAIN is set in sqlnet.ora, you can omit that part while calling the function
- Line breaks, white spaces and comments are removed