Installing and Configuring Oracle Client Software
In order to establish an ODBC connection to the Oracle database on your network in the mapping tool, you will need to have the appropriate Oracle ODBC driver and supporting software installed and configured correctly on your client computer.
If you already have the Oracle ODBC client and ODBC driver installed and are able to successfully establish ODBC connections to your Oracle database on the network using another ODBC-aware applications (such as Microsoft Excel, for example), then you can proceed directly to Connecting to Oracle to Define a Transformation Map.
There are several ways to obtain, install and configure the necessary Oracle client and ODBC software. Following are some observations from our own experience during testing with Oracle and creating these examples.
- We downloaded the Oracle Instant Client for Microsoft Windows (32-bit).
- We used version 11.1.0.7.0 after having had unresolved difficulties with earlier versions
- To the extent of our testing, we were able to successfully use this version with our Oracle 10g database.
- It was necessary to separately download and install the "Basic" and "ODBC" packages. The former is a pre-requisite for the latter.
- The installation process was manual. It was necessary to download and unzip files following the instructions provided, read and follow instructions in the associated README files, run the ODBC install script and modify and/or create environment variables. If any step is omitted or incorrectly completed, your ODBC client installation may not operate correctly.
- We encountered a number of issues and noticed other reports on the web. If you have difficulty connecting your Oracle client to your Oracle database on the network, we suggest you search resources on the web, using keywords associated with the symptoms you are experiencing. For issues that we encountered, it was our observation that somebody on the web had encountered similar symptoms and suggested workarounds or fixes are often available, either via Oracle resources or through peer-support networks and forums.
Following are some useful references related to downloading, installing, configuring and trouble-shooting the Oracle Instant Client software:
Oracle Instant Client Downloads
When you connect to an Oracle database using the Oracle ODBC driver, one of the things you are asked to specify is a TNS Service Name. The TNS Service Name is an Oracle artefact that specifies the information that identifies the server system and database to which you wish to connect.
TNS Service Name definitions are created and maintained using the Oracle SQL*Net program and are held in a text file named TNSNAMES.ORA. The Oracle client looks for this file in the location specified by the TNS_ADMIN environment variable.
The Oracle Instant Client "Basic" and "ODBC" packages mentioned above the necessary program for maintaining the TNSNAMES.ORA file. You can deal with this in one of the following ways:
- Install and use the full Oracle client instead
- Create the TNS Service Names on a different computer that has the necessary programs and then copy TNSNAMES.ORA to your client computer.
- Manually create and edit the TNSNAMES.ORA file using a plain text editor such as Notepad. To do this, you need to know the format and content of the file. Some of the links provided below describe the format and content of the file and/or provide examples.
In all the above cases, you must ensure that the TNS_ADMIN environment variable has been set correctly so that the Oracle ODBC driver software can locate the file.
Alternatively, where prompted for a TNS Service Name, you can often enter an SQL Connect URL to specify all the necessary connection information without using TNSNAMES.ORA. This is the approach that has been used in the examples described and shown later in this section. Whether you can use this approach may depend on your specific environment and database configuration.
Following are some references that provide more information on TNS Service Names, their definition and the alternate SQL Connect URL form. There are many more references available on the Internet. Use your Internet search engine to locate further references if required.
Local Naming Parameters (tnsnames.ora)