Sharing a Networked Oracle Home Directory

For ODBC Drivers

Revised 8/2/2002

Open Database Connectivity (ODBC) provides a means to access databases, such as Oracle, SQL, Access, and others from a local machine. The ODBC product from Microsoft provides the commonly used drivers, but the supporting modules necessary to use the ODBC interface may not be included. Windows 98/NT/2000/XP includes the ODBC Administrator for setting up the ODBC interface. The supporting modules are vendor specific and must be obtained from the proper vendor. Since this discussion is specific to the Oracle server, the rest of this document relates to Oracle specifically.

A successful Network Oracle Home Directory access using ODBC requires SQL*NET, Oracle ODBC, and supporting Oracle modules. This can be handled quite easily by installing these Oracle products on your local machine. Once installed, one can set up the ODBC Data Source and be on their way. This works fine for one workstation, but what about if you need to set up multiple users. You would have to install these products on each users machine, which creates multiple copies of the Oracle software; which you must ensure are in sync with the Oracle Home Directory.

Since a server already exists, why not use the server for all users to access. This is done by loading the Oracle ODBC product (SQL*NET, ODBC) on the server. As always, watch the Oracle version numbers. It is imperative that the Oracle Required Support Files (RSF) versions not be changed to incompatible versions.

Now that the server has been prepared, all users can access the server for required Oracle files. Remember the ODBC administration is still preformed on the local machine and the local machine must have the Microsoft ODBC installed. If it does not, it can be found on the Microsoft site.

In order for each user to access the Oracle files from the server, several registry changes must be made. As always, if you are not confident in changing the registry, I have copied the Microsoft warning below:

IMPORTANT: This article contains information about editing the registry. Before you edit the registry, make sure you understand how to restore it if a problem occurs. For information about how to do this, view the "Restoring the Registry" Help topic in Regedit.exe or the "Restoring a Registry Key" Help topic in Regedt32.exe.

Registry Changes:

Three registry changes need to be made in order to access the Oracle server products. It is not necessary to load any Oracle products on the local machine. However, it is required to have the Microsoft ODBC product installed on the local machine.

1. The following change will point the “Microsoft ODBC for Oracle” key to the proper drivers that exist on the server.

Oracle Version 7.x:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Driver=\\muntforms\orant\odbc250\SQO32_73.DLL

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Setup= \\muntforms\orant\odbc250\SQO32S73.DLL

Oracle Version 8.x:

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Driver=\\muntforms\oran60\bin\SQORA32.DLL

HKEY_LOCAL_MACHINE\Software\ODBC\ODBCINST.INI\Microsoft ODBC for Oracle\Setup= \\muntforms\oran60\bin\SQORAS32.DLL

2. In order to resolve access to SQL*NET, the application is dependent on knowing the location of the server. The application must include the path to the server. This requires a registry change for the specific application path as follows:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\

App Paths\

The “App Paths” defines the applications specific path(s) necessary to execute the application successfully. Under “App Paths” you will find listed many executeables on the local machine. If your application exists, click on it and change the path (adding to it) to include the following:

Oracle Version 7.x:

“;\\muntforms\orant\bin”

Oracle Version 8.x:

“;\\muntforms\orant60\bin”

Example:

HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\

CurrentVersion\App Paths\MSACCESS.EXE\

Oracle Version 7.x example:

This is the entry for Microsoft Access. The new value for the “Path” key is “H:\Program Files\Microsoft Office\Office\;\\muntforms\orant\bin”

Note: The only change to the Path was adding “;\\muntforms\orant\bin” to the end of the Path key for MSACCESS.EXE.

This would have to be done for any application being executed using the ODBC from the server. By the way, not all executeables are in this registry key. You can add your own, by creating a new key under HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\

CurrentVersion\App Paths\ using the executable name (e.g. Resource 25 executeable was “r25start.exe”, so the new key is “r25start.exe”).

3. The next step would be to set up the ODBC Data Source to access the desired database (Oracle version 7 example):

These changes can be made to the registry for user install purposes. It does require several changes to the registry. For those interested, I have included these changes at the end.

A “system error 126” error means that the Oracle product is not installed properly. Check your registry entries.

Registry Changes for Setting up the ODBC Data Source:

In order to set up the ODBC Data Source several registry keys must be modified:

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources\DSNNAME=Microsoft ODBC for Oracle

Where DSNNAME is the ODBC Data Source Name (see example below for r25report):

HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ DSNNAME=attributes

Where DSNNAME is the ODBC Data Source Name and

Attributes are ODBC data source specific (to find attributes use the ODBC administrator to create it on the local machine, then use these as a guide)

Example of attributes:

Note: These same changes have to be made for the registry key HKEY_USERS.