Resolve TNS alias using DBMS_TNS

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.

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

References

Kommentar hinterlassen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.