Quick Tip - Oracle 11g Instant Client

Oracle 11g has 2 versions of clients, Instant Client which has minimum required files for connecting to Oracle and the full blown (thick?) client which has many drivers and tools like SQL*Plus etc. The full blown version seems to be more for the developer and the instant client seems to be suitable for end users.

Recently, we upgraded to Oracle 11g database. When our desktop admin made changes to a user's PC to connect to our 11g database, one of the applications (in VB) stopped working. Incidentally, he had only installed Oracle instant client for end users, as it uses less space.

The VB application in question actually used OleDB to connect to Oracle. Apparently, the instant client we installed for this user did not include OleDB related files. This is why the connection failed. To make it work, we did the following:

1. Installed Instant Client for Oracle 11g (32bit) (option 1 during installation)

2. After the install we copied the OraOLEDB.dll files into the %ORACLE_HOME%\bin directory and registered it. (using regsvr32).

3. Created the directory C:\Oracle11g\Network\Admin and move over the tnsname.ora. (By default tnsnames.ora is left in Oracle11g folder. If you don't move the file, you need to add/change TNS_ADMIN environment variable to point to the right directory.)

Once we did this, the VB application was able to connect to Oracle 11g through OleDB.

Note:

1. This is the minimum required client installation for Oracle. We didn't install the full blown client, as it comes with SQL*Plus etc which the end user doesn't need.

2. All the data access drivers are bundled in an optional package called ODAC. We didn't want to install all the drivers for the user. So, copied only OleDB driver from here.

3. Apparently, SQL*Plus can be added on to instant client as well. See here for more information about Oracle instant Client. Question about connection strings has a hint for connection problems similar to ours.

Reference:
http://www.oracle.com/technetwork/database/features/instant-client/index.html

http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html

http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

http://support.dundas.com/Dashboard4.Installing_the_Oracle_Data_Provider_for_dot_NET.ashx