Finding a New Home for a Client on Windows

August 30, 2010

As I am sure that you are aware, there are compatibility problems between Oracle Databases and Oracle Clients that are from different releases.  The Oracle Database 11.1 documentation states the following:

“Upgrade Clients to the Current Release
You can upgrade any or all of your Oracle8i, Oracle9i, or Oracle Database 10g clients to Oracle Database 11g Release 1 (11.1). The Oracle Database 11g Release 1 (11.1) client can be used to access your Oracle8i, Oracle9i, Oracle Database 10g, and Oracle Database 11g Release 1 (11.1) databases.”

So, apparently the Oracle 11.1 client has no trouble communicating with a very old Oracle Database 8.1 release (I did not realize that the backward compatibility extended that far back).  But, what do you do if you are running a Windows client computer and need to able to access an Oracle 11.2.0.1 database and an Oracle 8.0.5 database?  Are you out of luck?  A recent thread on the OTN forums asked a question that was very similar.  In the thread I provided three possible solutions:

• If you have either the Professional or Ultimate editions of Windows 7. Download Microsoft’s free Windows XP Mode virtual machine and install the Oracle 9i client in that virtual machine. You should be able to create an icon that automatically launches the Windows XP mode virtual machine and then runs a specific program inside the virtual machine (I have not yet tested the auto-launch feature).
• If the programs will use an ODBC connection, install one of the Oracle clients, create the ODBC connection that will use that client, install the other Oracle client, and create the ODBC connection that will use that client.
• Modify the environment and start the program that will access the database using that modified environment.

Let’s take a closer look at the last option.  Let’s assume that we have an Oracle 11.1.0.7 home set up on the computer, with the bin folder located here:

C:\oracle\product\11.1.0\db_1\bin

When a program starts on Windows, typically the current folder (directory) is searched for required support files, and then the folders in the system path (defined by the PATH environment variable) are checked in the specified order for the support files.  So, to change to this Oracle home we just need to place this location of our home’s bin folder at the start of the PATH environment variable.  To change that variable, open a Windows command prompt window (run cmd.exe) and type the following commands (note that the last command will cause the command prompt window to close):

SET PATH=C:\oracle\product\11.1.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The first line appends the selected Oracle home to the beginning of the PATH variable.  The second line starts SQL*Plus in a new window, with Windows automatically finding it in the modified search path; what is interesting is that SQL*Plus (or any program for that matter) remembers the PATH variable that was modified by the previous command (we could have modified several environment variables).  The end result might look like the following (assuming that you did not press the Enter key after typing EXIT – the background window will disappear if you pressed the Enter key):

Interesting, but how do we know that SQL*Plus is remembering the modified PATH variable?  We are able to confirm that the modified path is remembered using Microsoft’s Process Explorer utility by double-clicking on the sqlplus.exe entry in the program and then switching to the Environment tab:

As you are probably able to determine, I probably also should have changed the PERL5LIB location.  Now, let’s try a 8.1.7 client on the same computer:

SET PATH=C:\orant\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The resulting window will look like this, if you did not press the Enter key after typing EXIT  – the background window will disappear if you pressed the Enter key:

And the Process Explorer window might look like this:

Why stop at two examples?  We could do this in a command prompt window assuming that there are four Oracle homes):

SET PATH=C:\oracle\product\10.2.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\oracle\product\10.2.0\client_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\oracle\product\11.1.0\db_1\bin;%PATH%
START CMD /K SQLPLUS /NOLOG

SET PATH=C:\orant\bin;%PATH%
START CMD /K SQLPLUS /NOLOG
EXIT

The end result, after manually aligning the windows, might look like this:

It does not need to be SQL*Plus that is started – it could be any program.  OK, but I don’t want to type a bunch of commands to launch the program.  No problem, just create a text file with a .bat extension:

If the default icon is not fancy enough, just right-click the file and create a shortcut.  You can then view the properties of the shortcut to change the icon:

Just about any EXE file will contain a couple of icons, as will some DLL files.  The result looks like this:

If we were using this approach to start an application program that uses a specific Oracle home, it probably would make sense to change the properties of the shortcut to run using a minimized window to prevent a brief screen flash to black when the batch file executes.