Find path of TNSNAMES.ORA 1

[subhavs]

Hey...

How do I find out the path of the TNSNAMES.ORA file from inside a program ?

My VB application will be installed on Win95 / WinNT machines with different versions of ORACLE 8. Since the path for the TNSNAMES.ORA is different for each of the versions, I cannot hardcode it's path inside the program.

Thanks in advance for the help,

- Subha Nothing is impossible, even the word impossible says I'm possible.

 

[listener22]

One option --
have the user identify it using a file dialog or other tool. Jim

oracle, vb

 

[karluk]

I don't know how to get the tnsnames.ora location directly from Oracle. Instead I would attempt to derive it from the Oracle version, which you indicate determines the location.

Can you query v$instance from your VB application? The column named "VERSION" tells you what version of Oracle is running. The application could then translate this into a tnsnames.ora file location.

 

[subhavs]

Thanks for both the responses.

I was more looking into reading Windows Registry or something like that. I don't really want the users to determine the path.

When ORACLE is installed, does it create any Registry entries which I can read to detemine where the Net8 stuff is stored ?

- Subha Nothing is impossible, even the word impossible says I'm possible.

 

[DBAwhosaysNIE]

look for ORACLE_HOME in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

there are two problems I can foresee you having:

1) there can be multiple versions of Oracle Client under different homes so user input might still be required to specify a specific one.

2) all versions of Oracle Client (that I'm aware of) have tnsnames.ora in $ORACLE_HOME/network/admin but (for some bizarre reason) 8.0.x clients also have one in $ORACLE_HOME/net80/admin where Net8 entries go.

good luck!

 

[karluk]

Yes, forget my previous suggestion. If the issue is identifying the version of the Oracle client on various machines, you won't be able to query the server - client information is simply unavailable.

Let me give it another try. I think DBAwhosaysNIE's advice is pretty good. Here are some further observations based on the configurations I see on my own machines.

1. Find out how many Oracle homes exist on the client machine by inspecting HOME_COUNTER in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES.

2. If HOME_COUNTER=1:

2a. Get the value, if any, of NET80 in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.

2b. If NET80 has been set, the directory $NET80\ADMIN contains TNSNAMES.ORA.

2c. If NET80 has not been set, get the value of ORACLE_HOME in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE, as DBAwhosaysNIE suggests. Then the directory $ORACLE_HOME\NETWORK\ADMIN contains TNSNAMES.ORA.

3. If HOME_COUNTER>1, then you have to decide which version of Oracle client your application will use. The registry entries for each separate Oracle install appear to be defined in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME1, etc.

3a. Let's say you (arbitrarily) decide to run your application using the first Oracle client. Then repeat steps 2a through 2c to get the location of TNSNAMES.ORA, except that you will get registry entries ORACLE_HOME and NET80 from HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0 instead of HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE.

I hope this helps. The above logic would correctly identify the location of TNSNAMES.ORA on my machines, but of course I can't guarantee that it will always work.

 

[subhavs]

Thanks for all your responses.

I will try Karluk's solution and see how it goes.

- Subha Nothing is impossible, even the word impossible says I'm possible.