Re: Easy way to distribute a new entry into the TNSNames File..

Alley wrote:
> Hi, I need to see if I can get some advice on the best way to do this,
> I am not a DBA so bear with me please..
>
> I have a VB app that the exe runs from a network drive.. We just added
> a new Oracle back end and I need to have all the current users be able
> to use the new Oracle features..
>
> I really dont want to have to go into each users TNSNames file and
> insert the new entry for the Oracle DB..
>
> TNSNames Entry:
> JIDB_ORACLE =
> (DESCRIPTION =
> (ADDRESS_LIST =
> (ADDRESS = (PROTOCOL = TCP)(HOST = mickey.mouse.mil)(PORT =
> 1111))
> )
> (CONNECT_DATA =
> (SERVICE_NAME = devel)
> )
> )
> VB Connection String:
> With gOCnn
> .CursorLocation = adUseClient
> .ConnectionString = "Driver={Oracle ODBC Driver};" & _
> "Dbq=JIDB_ORACLE;Uid=" & uid & ";Pwd=" & pw
> .Open
> End With
>
> I found a posting that said to put a TNSNames file on the network
> where the VB app is with only the New DB entry (JIDB_ORACLE)..
> Ok, But I am not getting how this works.. How does my machine know to
> look there instead of the local TNSNames.. And if I run another app
> that uses the local TNSNames services will it know to look in the
> network one and if it does not find the service name to look in the
> local one??
>
> I am researching if in my VB connection string I can give it a path
> for the TNSNames file, but I dont remember seeing anything like that..
>
> Help....
> Thanks Alley

Welcome to dba hell. While it's going to work if you put a local tnsnames.ora in the same directory as your VB program (to be precise: into the working directory that gets assigned at the startup of your program), which I've got to admit I've done occassionally in the past. But even if it's more effort for you - if there is any way to get the new Oracle Net Services Alias into your 'official' tnsnames.ora, either by distributing it to every client or by pointing your clients to an network path using TNS_ADMIN, then please do it.

I don't know why Oracle (at least on windows, I never checked for *nix) actually searches in the working directory first (and I'll probably get a good talking to from Sybrand because it's burried somewhere in the docs) but really only consider that if there is no other way.

Else if your program isn't connecting, you'll have to remember that it's actually started from a network drive and you have to change to that directory if you want to do such basic things as tnsping or sqlplus to the new server. It will bite you in the back at a time when you're under pressure and strangely enough, under pressure most people tend to forget those little but important details...

So to explicitely answer your question: for some (strange?) reason, the Oracle Net Services Client on windows searches in your working directory for a tnsnames.ora. If it can find one with a matching Net Services Alias, it will use it, else it will use the one that's in $ORACLE_HOME/network/admin or if it's defined, the one TNS_ADMIN is pointing to.

But depending on what your application does, it might still not work because this working directory stuff can get tricky. For those interested: grab a copy of sysinternals' filemon (www.sysinternals.com) and have a look where Excel has it's working directory if called

a) by double clicking on an .xls file using windows explorer
b) by invoking excel through the start menu
c) using the 'recent documents' menu

You'll begin to understand that a tnsnames.ora anywhere else than the default destination is just to keep someone busy.

Cheers
Holger Received on Sun Oct 17 2004 - 13:58:24 CDT