Remote connection to Oracle 10g Express via ODBC

For some strange reason the Oracle 10g Express installation will not run the configuration web client for me but it works all fine for the testing folks. But it installs happily in a virtual PC, and it runs/configures just fine.

Now to connect from my PC I need some client stuff. On the Oracle 10g Express page it has a client download, which you would think would do the business. But does not. The current approach in the office is to install the complete Express server and just ignore the server parts. That idea stinks, so with some help from a co-worker I got the correct option work.

Install the Instant Client, for ODBC (god bless VB6.0 applications) you need to:

  1. Download the Instant Client Package - Basic and Instant Client Package - ODBC

  2. unzip them to C:\\Oracle\ and the will make a instantclient_10_2 subdirectory. The resulting directory C:\\Oracle\\instantclient_10_2 will be referred to as <dir> from now on.

  3. run <dir>\\odbc_install.exe

  4. create a file in <dir> called tnsnames.ora with the contents:

    Name Of TNS =  
    (DESCRIPTION =  
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_server_name)(PORT = 1521))  
    (CONNECT_DATA =  
    (SERVER = DEDICATED)  
    (SERVICE_NAME = XE)  
    )  
    )  

    replacing your_server_name with the name/ip address of you remote server. You can name you TNS with a sensible name by replacing Name Of TNS

  5. Add <dir> to the PATH environment variable

  6. Add new environment variable TNS_ADMIN with value <dir>

  7. Now you can use the Data Source (ODBC) tool to setup the DSN like via the Oracle in instantclient 10_2 Driver

Done!

Updated: Changed the naming the TNS part to be correct.

Share

Comments:

Simeon Pilgrim 2008-11-12 10:56:06

After installing on a new PC I was getting the error:
“The setup routines for the Oracle in instantclient10_2 ODBC driver could not be loaded due to system error code 126.”

I had to copy mfc71.dll and msvcr71.dll from the Win\System32 directory into the <DIR> to make the client happy. Odd.


John 2008-07-10 03:04:56

Good Article!! Thanks


Naveen 2009-10-16 20:01:31

Great Article… i was trying to install oracle 10g XE client on a Win 64bit machine and this article helped….
thank you


Simeon 2009-10-17 17:14:46

You are welcome


Bruno 2010-06-05 10:05:10

I have the same problem you had, but I’ve found a simpler solution: Just install oracle client for 11g.
The ODBC driver in there worked just as fine with Oracle XE.

Now, about the licenses, I’m not sure if it’s free or whatever, let me know if you ever look into that, cya.


Hakim Amir 2010-12-20 05:37:47

I am a student. I have an VB6 Application which uses Oracle 10g at it backend. Can I connect the same application from a client machine with the help of Oracle XE Client to the central Oracle XE Server DB.

Also can you please tell me how to connect a Oracle XE Client with a Oracle XE server.


športna trgovina 2011-06-30 07:42:11

Where do I change the database to accept remote cennections?


Simeon 2011-04-07 17:17:06

Yes you can, change the your_server_name part in (HOST = your_server_name) to the the remote servers name, or it’s IP address.