Instead of installing the full Oracle Client plus ODBC drivers, you can use Oracle "Instant Client" plus Instant Client ODBC supplement. Setting it up is not intuitively obvious, so here's what I did. Note that you do NOT need a tnsnames.ora or a sqlnet.ora.
- PC = Windows XP Pro SP3 with 4GB RAM
- Oracle server is 10g version 10.2.something
- Instant Client for Win32 download from http://www.oracle.com/technology/software/tech/oci/instantclient/index.html [1] (two downloads)
- Instant Client Package - Basic
- Instant Client Package - ODBC
- Unzip the Basic Instant Client. All of the files are now in C:\instantclient_10_2.
- Unzip the ODBC supplement. Put all of its files in C:\instantclient_10_2.
- Run C:\instantclient_10_2\odbc_install.exe
- Set the following SYSTEM (not user) environment variables:
- LD_LIBRARY_PATH=C:\instantclient_10_2
- TNS_ADMIN=C:\instantclient_10_2
- SQLPATH=C:\instantclient_10_2
- AppendC:\instantclient_10_2 to your PATH
- Start/Control Panel/Data Sources(ODBC)
- System DSN
- Add:
- Data Source Name = whatever you want to call it (I used P1EBETL)
- Description = whatever you want to say (I used P1EBETL)
- TNS Service name = //hostname:port/serviceID (I used //p1bid1d1.edc.mycompany.net:1524/P1EBETL)
- Note: This is NOT a TNS Service name -- it is a "SQL Connect URL." Using this means you don't have to create a tnsnames.ora or a sqlnet.ora, and it might be essential to not installing SQL*Net. (I don't know for certain. I just know that by using it, I didn't have to install other software or create the .ora files.)
- User ID = database-user-ID-here
- Press Test Connection, enter the password, and click OK. You should see a "Connection Successful" dialog.
Update 9/4/2008: This was working well for me and then one day it suddenly stopped. I started getting the following error:
unable to connect sqlstate s1000 oracle odbc ora ora-12705 cannot access nls data files or invalid environment specified
The solution was to run Regedit, and delete: HKLM\Software\Oracle\NLS_LANG