OraFAQ Forum: Client Tools » Help with tnsname.ora

Home » Other » Client Tools » Help with tnsname.ora (Oracle client 12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help with tnsname.ora [message #678865]

Fri, 17 January 2020 13:37

dtran
Messages: 5
Registered: January 2020

Junior Member

Hello,
I am new to Oracle and I would like to seek your help and expertise regarding the tnsname.ora file. I have a very simple visual basic program running in window 8 that I have to maintain , and all it does is to connect to Oracle database. The other coder before me said that I need to have the tnsnames.ora file at C:\Oracle\Client\NETWORK\ADMIN

Now we are doing an upgrade to window 10 in a new machine, and I need to run the same visual basic program in the new window 10 machine.
I just downloaded an Oracle Client 12c to my window machine, and I just installed it. The oracle installer said the Oracle base directory is installed at D:\app\client\Tom,
and the Oracle Home directory is at D:\app\client\Tom\product\12.2.0\client_1

In the Oracle home directory, I see that I have the tnsnames.ora file located inside of the D:\app\client\Tom\product\12.2.0\client_1\network\admin.

My question to you is that since the visual basic program said that I need to have the tnsname.ora in the C:\Oracle\Client\NETWORK\ADMIN but now I am having it at
D:\app\client\Tom\product\12.2.0\client_1\network\admin\tnsname.ora. So could I simply copy just only 1 file namely the tnsname.ora from D:\app\client\Tom\product\12.2.0\client_1\network\admin\ to a new created directory called C:\Oracle\Client\NETWORK\ADMIN ? Would it work or do I need to do something else?

Thank you so much in advance for all of your helps.

Tom

[Updated on: Fri, 17 January 2020 13:42]

Report message to a moderator

Re: Help with tnsname.ora [message #678866 is a reply to message #678865]

Fri, 17 January 2020 14:22

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Your Visual Basic code does not need to know where the tnsnames.ora is located.
By default, it should be in %ORACLE_HOME%\network\admin directory (which is your case).

Note: if you want to continue to get help from the board, you should envisage to feedback in your topic.

John Watson wrote on Thu, 16 January 2020 08:45

Welcome to the forum. Please read the OraFAQ Forum Guide and ...

Report message to a moderator

Re: Help with tnsname.ora [message #678867 is a reply to message #678866]

Fri, 17 January 2020 14:44

dtran
Messages: 5
Registered: January 2020

Junior Member

Thank you so much for all of your helps.
I apologize for making a mistake in my question from the previous thread.
My %ORACLE_HOME%\network\admin directory contains only the sqlnet.ora, and
%ORACLE_HOME%\network\admin\sample directory contains both the tnsnames.ora and sqlnet.ora. Please correct me if I am wrong that in my case, I need to move the tnsnames.ora to my My %ORACLE_HOME%\network\admin, right?

Also for my understanding on the location of tnsnames.ora, since my tnsnames.ora file is resided at %ORACLE_HOME%\network\admin\; so the VB script can find it and it does not need to know where the file is located.

Thanks again for all of the helps.
Tom

Report message to a moderator

Re: Help with tnsname.ora [message #678868 is a reply to message #678867]

Fri, 17 January 2020 15:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Below is FYI & for example puposes

Setting the TNS_ADMIN environment variable on a Microsoft® Windows® 2000 and 2003 system:

On the desktop Right-click on My Computer and select Properties > Advanced tab
Click the Environment Variables button.
In the System variables section click the New button
Enter a variable name of TNS_ADMIN then enter a variable value of the path to the TNSNAMES.ORA file that will be used as the default.
For example:

Variable Name TNS_ADMIN
Variable Value C:\Oracle\Ora8i\Network\Admin

Click OK
Reboot the machine for the changes to take effect.

Report message to a moderator

Re: Help with tnsname.ora [message #678869 is a reply to message #678867]

Sat, 18 January 2020 01:04

Michel Cadot
Messages: 68749
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The file in %ORACLE_HOME%\network\admin\sample is just an example of tnsnames.ora file to help you write yours.
The file used by the programs must be in %ORACLE_HOME%\network\admin.
You have 2 ways to build a valid tnsnames.ora:

1/ Copy the tnsnames.ora from sample directory into ?/network/admin, open it with notepad and create an entry using the example one

2/ Using the Oracle Network Configuration Assistant with one the 2 methods
2a/ Open a DOS box, verify that ORACLE_HOME and PATH are correctly set, type "netca"
2b/ Click on Windows "Start" button, scroll down to "Oracle..." folder, then to "Configuration and migration tools" and click on "Oracle Net Configuration Assistant"
then follow the screens.

Report message to a moderator

Re: Help with tnsname.ora [message #678899 is a reply to message #678868]

Tue, 21 January 2020 10:28

Minh.Tran@emoryhealthcare
Messages: 5
Registered: January 2020

Junior Member

Thank you so much for the help. May I ask a question regarding the setting up of TNS_ADMIN environment variable in window ?
I am not clear on why do we need it since I have the tnsnames.ora file and I guess that my Power Shell script would have known where the tnsnames.ora file is ,
and I do not need to define the TNS_ADMIN environment variable. Please correct my assumption if it is not correct.

My simple Power Shell script is as follow:

$domain = "TST"
$swtsloc = "myid"
$password = "mypassword"
$adOpenStatic = 3
$adLockOptimistic = 3
$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$objRecordset.CursorType = 2
$objRecordset.CursorLocation = 3

$sql = "select * from wts.locations where lower(clientname) = lower('$sWtsLoc')"

Try
{
$objConnection.Open("Provider=MSDAORA;Data Source=$($domain)1;User Id = ehcjob;Password =$password")
}
catch
{
HandleError "Could Not open Database. Ensure tnsnames.ora and sqlnet.ora are located in C:\Oracle\Client\NETWORK\ADMIN and that their configuration is up to date."
$error_occurred = $true
}

In case if I really need the TNS_ADMIN environment variable, then should I add it as variable path to my simple power shell script?

Many Thanks for all of the helps.

Report message to a moderator

Re: Help with tnsname.ora [message #678901 is a reply to message #678899]

Tue, 21 January 2020 11:40

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

The Oracle client software has a specific, well documented sequence in which it will search for tnsnames.ora. First, it will check if the environment variable TNS_ADMIN has been set. It doesn't matter whether it is set as a system-wide variable and inherited by the application process, or if it is set by the application process itself. If it is set in the enviornment of the called client, it will be used to locate sqlnet.ora and tnsnames.ora. If the variable is not set, or no file is found at that location, the client will check the current directory. If not there, it will check %ORACLE_HOME%\network\admin.

And it looks to me like your code will report to check tnsnames regardless of the actual error. Suppose your connection fails due to bad user credentials? Supposed it fails because the database is down? I think you'd be better of not to 'catch' at all and let oracle report more precisely what went wrong.

Report message to a moderator

Re: Help with tnsname.ora [message #678903 is a reply to message #678901]

Tue, 21 January 2020 11:57

Minh.Tran@emoryhealthcare
Messages: 5
Registered: January 2020

Junior Member

Thank you so much for the detailed explanation on the Oracle client and how it searched for the tnsnames.ora.

Report message to a moderator

Re: Help with tnsname.ora [message #678907 is a reply to message #678903]

Tue, 21 January 2020 13:21

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Minh.Tran@emoryhealthcare wrote on Tue, 21 January 2020 11:57

Thank you so much for the detailed explanation on the Oracle client and how it searched for the tnsnames.ora.

You will find more detail here.

And don't forget to address the bug in your error handling.

Report message to a moderator

Re: Help with tnsname.ora [message #678909 is a reply to message #678907]

Tue, 21 January 2020 13:51

Minh.Tran@emoryhealthcare
Messages: 5
Registered: January 2020

Junior Member

Yes, Thank you so much. I am going to rewrite my error handling so that we will cover important events such as failing database, and login and password problems as you have mentioned earlier from the above thread.
Since we are discussing my simple Power Shell script, could I ask a quick question regarding the ADODB.Connection im my simple script ? Since I am new to power shell , may I ask if I bneed to import a package namely ado from the library in order to use it in the script ? The reason I was asking because I normally import packages in python or java before I call it. In my case, I do not think that I need to import ado because I already installed the Microsoft Framework 3.4 in my window machine, so I guess that I am covered. Please correct my assumption if it is wrong.

Report message to a moderator

Re: Help with tnsname.ora [message #678910 is a reply to message #678909]

Tue, 21 January 2020 14:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

https://lmgtfy.com/?q=sample+powershell+script+to+connect+oracle+database

Report message to a moderator

Re: Help with tnsname.ora [message #678911 is a reply to message #678909]

Tue, 21 January 2020 14:54

Minh.Tran@emoryhealthcare
Messages: 5
Registered: January 2020

Junior Member

One more thing to ask regarding the power shell script connect to Oracle data base. On this example https://www.c-sharpcorner.com/blogs/how-to-connect-oracle-database-using-powershell-script , it uses the Oracle.DataAccess.dll such as
#Load the assembly file (Oracle.DataAccess.dll) from the server you are running the script from
$AssemblyFile = “C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"

I wonder if the dll is needed and why since we already have the tnsname.ora ?

Report message to a moderator

Re: Help with tnsname.ora [message #678913 is a reply to message #678911]

Tue, 21 January 2020 18:14

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Minh.Tran@emoryhealthcare wrote on Tue, 21 January 2020 14:54

One more thing to ask regarding the power shell script connect to Oracle data base. On this example https://www.c-sharpcorner.com/blogs/how-to-connect-oracle-database-using-powershell-script , it uses the Oracle.DataAccess.dll such as
#Load the assembly file (Oracle.DataAccess.dll) from the server you are running the script from
$AssemblyFile = “C:\oracle\product\11.2.0\client_1\ODP.NET\bin\2.x\Oracle.DataAccess.dll"

I wonder if the dll is needed and why since we already have the tnsname.ora ?

They serve entirely different functions.

.dll files are binary executables.

tnsnames.ora is simply a 'telephone directory', allowing the client binaries to look up the network address of a database.

It's like asking if you really need an engine in your car when you already have a Michelin road map.

Report message to a moderator

Re: Help with tnsname.ora [message #678925 is a reply to message #678913]

Wed, 22 January 2020 08:12

dtran
Messages: 5
Registered: January 2020

Junior Member

Thank you so much for the explanation.

Report message to a moderator

Previous Topic:	Writing QBE's that are longer than the field length
Next Topic:	How to call external program from SQL*Plus and catch its output into anonymous PL/SQL block

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Mar 12 02:49:37 CDT 2025