0

I am building a box with Windows XP with some applications. One application requires connection to an Oracle database on remote. I have installed OracleXEClient.exe from Oracle download. The installation does install "Oracle Provider for OLE DB" driver.

My problem is that I still cannot make connections to the remote Oracle db. The test I have done is to create a UDL file with Oracle provider OLE DB connection. The error message is:

---------------------------
Microsoft Data Link Error
---------------------------
Test connection failed because of an error in initializing provider. ORA-12154: 
TNS:could not resolve the connect identifier specified

I think I may miss TNSNAMEC.ora in the box. I can find this file from another box where Oracle connection works fine. I am not sure what package I should install (from Oracle) so that the default TNSNAEMES.ora will be installed with related files and setup path for accessing the TNS file?

Not sure if I can just copy the TNSNAMES.ora and add the dir to environment PATH, manually?

Improve this question

4 Answers 4

Reset to default
1

Oracle XE client does not have a tnsnames.ora.

To connect using the odbc use the instructions on this page

or, as Gary said, c:>sqlplus /nolog

SQL> connect user/[email protected]:1521/xe should work as well.

Improve this answer
1

If you have the XE client, you have sqlplus. That is where I would start testing connectivity. Try a connection in the following style

sqlplus user/[email protected]:1521/xe

substituting the user, pwd and put in the IP address where your XE database is running. If that works, take a step back and put in the hostname rather than the IP address. If that works, try 

tnsping dbname

I'm assuming it won't find the connection. But it will tell you something like "user parameter files: " with a sqlnet.ora file in a directory ending in network/admin Put the tnsnames.ora file in that directory (as that is where it will look for it by default).

Then retry the tnsping (with whatever label the database is given in the tnsnames.ora file). If that doesn't work, you may need to amend hostnames or ip address values in the tnsnames.ora file.

If tnsping can find it, then you should be good to go with a 

sqlplus user/pwd@dbname

And once that it working, give it a go with the OLE DB

Improve this answer
2
  • interesting enough, I don't have tnsping in my xeclient package.
    – David.Chu.ca
    Apr 16, 2010 at 20:15
  • They've omitted that ? Okay, time to search the drive for a sqlnet.ora file somewhere under a network/admin directory.
    – Gary
    Apr 19, 2010 at 6:28
0

I'm not familiar with the OracleXEClient.

With the traditional Oracle fat client, you need to have a system environment variable for TNS_ADMIN, which points to the folder where tnsnames.ora is located. And you probably need to log off/logon for that variable to be picked up in your environment.

You also need to ensure that the file sqlnet.ora exists in that same folder, and specifies that you are using tnsnames. Example:

NAMES.DIRECTORY_PATH= (TNSNAMES, LDAP)

Improve this answer
2
  • In the box that connection to Oracle is workin, I cannot find this TNS_ADMIN (set command in cmd). In the path, there are two items, one as C:\oracle9\bin and one as C:\oracle9\jre\1.1.8\bin. In addition to that, there are some folders in c: such as C:\oracle8, C:\oracle9, C:\oracle10, and C:\orant.
    – David.Chu.ca
    Apr 15, 2010 at 18:20
  • This is the information about ORAcleXEClient: "Oracle Database 10g Express Client. Client software to be installed on client systems for remotely accessing Oracle Database 10g Express Edition. You do not need to install the client onto the same computer as Oracle Database 10g Express Edition."
    – David.Chu.ca
    Apr 15, 2010 at 18:21
0

What is the ORACLE_HOME directory for your Oracle Client installation? It will likely be a subfolder of C:\oracle\product\, although I'm not familiar with XEClient installations. In a typical Oracle 10g client install, the home directory is C:\oracle\product\10.2.0\client_1. Assuming this directory, then your TNSNAMES.ORA file is located in C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN. Drop your working TNSNAMES.ORA file in that directory and test again.

TNS_ADMIN is useful in installations with multiple oracle homes. It is not required.

Improve this answer
1
  • I cannot find something like C:\XEClient\product\xxx.x.x\client_1. The home dir is C:\XEClient and there are many sub dirs there, such as bin, and more. I copied my ora files to C:\XEClient\network\admin. Still the udl cannot make the connect.
    – David.Chu.ca
    Apr 15, 2010 at 19:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .