Connect without an entry in tnsname.ora

[richyip]

Each client has its own tnsname.ora on the local PC. Let say I want to connect to a new location instead from the tnsname. How can I connect?

I used 'sqlplus system/admin@172.20.21.15:1521:orcl' where orcl is the service name. I got an error ORA-06401: NETCMN: invalid driver designator. Pls help on how to connect without a tnsname entry. Tks.

[jovery]

You can do it by specifing the connect string after the @ symbol

eg


sqlplus scott/tiger@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=server_a)(Port=1521))(CONNECT_DATA=(SID=db_a)))


HTH

[stecal]

Originally posted by richyip
Each client has its own tnsname.ora on the local PC.
Why? Make one tnsnames.ora file, place it on a shared network drive, and set TNS_ADMIN environment variable (System Properties>Environment)to the shared directory location. Add sqlnet.ora there too if you are using it.

[mike2000]

but you have to add the registry to point to the network beside system environment variable

[stecal]

That is so wrong, it is wronger than wrong.

[mike2000]

can you explain why , please Stecal????

[stecal]

Without getting into all the high tech MCSE knowledge that is running around in my head, you do not need to do anything with the registry in this case. And for that matter, there is little you need to do with the registry on a routine basis. Adding an environment variable, either as a system variable or as a user variable, DOES NOT require any editing of the registry. You can see this for yourself. Add an environment variable named something odd or unique ("mytestenv") and set it to whatever. Close the system properties. THEN go look in the registry. Search for the variable. Did you have to set it? No. It is done by PFM (a form of Windows magic). Use regedit instead of rededt32 (the find key searches across the hives at the same time). If you don't want to wait on the search, go to HKEY_CURRENT_USER/Environment.

[marist89]

Originally posted by stecal
Without getting into all the high tech MCSE knowledge that is running around in my head, you do not need to do anything with the registry in this case. And for that matter, there is little you need to do with the registry on a routine basis. Adding an environment variable, either as a system variable or as a user variable, DOES NOT require any editing of the registry. You can see this for yourself. Add an environment variable named something odd or unique ("mytestenv") and set it to whatever. Close the system properties. THEN go look in the registry. Search for the variable. Did you have to set it? No. It is done by PFM (a form of Windows magic). Use regedit instead of rededt32 (the find key searches across the hives at the same time). If you don't want to wait on the search, go to HKEY_CURRENT_USER/Environment.
Do you have to reboot? ;)

[stecal]

Actually, this is the ONE time you don't have to reboot. An oversight on Microsoft's part, I'm sure.

[richyip]

jovery,

Yeah this is my connection:
sqlplus system/admin@172.20.21.15:1521:test

test is my service name and I got the error. So is there any problem with the connect string?

[ales]

Originally posted by richyip
So is there any problem with the connect string?Exactly.
jovery has posted the form of the connect descriptor you have to use.
The form you used is jdbc-like connect string and does not work in sqlplus.
Try:
sqlplus system/admin@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=172.20.21.15)(Port=1521))(CONNECT_DATA=(SID=test)))

[mike2000]

STECAL and other peer,

per your advises, I am trying to do the same thing as you show and I just want to double check if I did it right.

set TNS_ADMIN environment variable: like this?? TNS_ADMIN=$ORACLE_HOME\NETWORK\ADMIN and this oracle home is somewhere on the network.

that's all you have to do without modifying or adding anything to the regedit????

thanks

[stecal]

variable: TNS_ADMIN
value: $ORACLE_HOME\network\admin
What you actually type in, assuming your $ORACLE_HOME resolves to d:\ora81:
value: d:\ora81\network\admin

TNS_ADMIN could point to c:\my documents, \\some_server\directory, k:\some_other_path_on_a_mapped_network_drive and so on - it does NOT have to point to ANY ORACLE_HOME; it is simply a directory location, one that you must be able to read from.

[stecal]

Well?

[mike2000]

Thanks Stecal

[KenEwald]

I'm into centrally managed connections. Consider this option.

Put your connection entries in Oracle Internet Directory and simply point clients there, like this:

client $ORACLE_HOME/network/admin/<b>sqlnet.ora</b> file:

NAMES.DIRECTORY_PATH = (LDAP)

client $ORACLE_HOME/network/admin/<b>ldap.ora</b> file:

DIRECTORY_SERVER_TYPE = OID
DIRECTORY_SERVERS = (YourOidServerIP:Port:SslPort, YourBackupOidServerIP:Port:SslPort, etc...)