Referring external file in TNSORANAMES.ora

Question

I am trying to write a VB script to add/edit/delete some entries in tnsnames.ora. It will be convenient to refer/modify an external file (say myProjectOraNames.ora) rather than modifying tnsnames.ora

With this configuration (assuming it is possible), the Oracle client should refer to both the existing tnsnames.ora file and my new myProjectOraNames.ora to get service details.

Is there any option/configuration can be done to achieve this functionality?

Answer 1

You can configure the primary tnsnames.ora file to use other files as well using the IFILE command.

On my laptop, for example, I have separate tnsnames.ora files for various clients. So my tnsnames.ora file looks something like

JCAVE11G.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <<ip address>>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jcave11gr2)
    )
  )


IFILE = c:\oracle\product\11.1.0\db_1\Network\Admin\tnsnames.ora.client1
IFILE = c:\oracle\product\11.1.0\db_1\Network\Admin\tnsnames.ora.client2
IFILE = c:\oracle\product\11.1.0\db_1\Network\Admin\tnsnames.ora.client3

You could do something similar where the primary tnsnames.ora file has an IFILE reference to your myProjectOraNames.ora file

Answer 2

Risking some conflicts or malfunctions, you can always determine location of directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located - using TNS_ADMIN variable. In your script you can do something of the following nature:

set TNS_ADMIN=\some\path

(Make sure that \some\path has all th e required sql*net files.)

and then proceed with updates - a client running with this context would look up for files under \some\path. However, I do not think you can have multiple paths defined for this environment variable.