How to connect to Oracle with Oracle Instant Client and InfoFind

Overview

This document provides a step by step guide on how to set up an ODBC Connection to an Oracle Database with Oracle Instant Client. Furthermore if you would like to connect to and query Oracle with InfoFind then this document will show you how to do that as well. InfoFind is free to download from this web site and has many useful database features and system utilities. In fact filtering through large Oracle Databases with InfoFind is very fast and the premium version of InfoFind will allow Excel Reports from Oracle with the click of a button. To follow the steps in this guide basic computer knowledge such as how to move, create, and edit files is required. In addition ODBC experience is helpful. Oracle can be difficult to setup and use for many users so Omnicognic works hard to provide the easiest to read documents on this topic, and useful and efficient database tools.

Step 1 - Download Files from Oracle’s Web Site

http://www.oracle.com/technology/tech/oci/instantclient/index.html
Download the files:

• OCI Instant Client Package for Basic or Basic Light.
  (Basic Light supports only Western Character Sets while Basic supports Unicode.)
• ODBC Supplement

Step 2 – Unzip Files

Unzip all downloaded files to a directory such as [C:\Program Files\Oracle\instantclient_11_1]. The folder will need to be created.

Step 3 – Run Script

In the folder where you saved the files run the program [odbc_install.exe].

Step 4 - Set Environment Variables TNS_ADMIN and PATH

1. Open the System Information form in InfoFind from the menu [Computer]->[System Information]. On the toolbar on the form click the second button [Open System Properties].

   

2. The Windows System Properties Dialog will be displayed. Select the Advanced Tab and click the [Environment Variables] button.

   

3. Edit the System Variables to modify the variable Path and to add the variable TNS_ADMIN. The values for the variables will be as follows: Path = {Path};C:\Program Files\Oracle\instantclient_11_1\
   TNS_ADMIN = C:\Program Files\Oracle\instantclient_11_1

   

Step 5 – Create the file tnsnames.ora

Create a blank text file [C:\Program Files\Oracle\instantclient_11_1\tnsnames.ora]. Open the file and enter the following:

{Database Name}=
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = {hostname})(PORT = {port}))
      )
   (CONNECT_DATA =
      (SERVICE_NAME = {sid})
   )
)

At minimum you will need the Oracle sid and hostname. If you do not know this information then you may need to get it from your administrator. The standard port is 1521 and the Database Name can be whatever you want. When entering the Database Name if you have a space in-between the name and the equal sign [=] then you may need a space when creating the ODBC connection so keep this in mind if you have problems connecting later. An example of a valid file can be:

Oracle=
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = OracleServer)(PORT = 1521))
      )
   (CONNECT_DATA =
      (SERVICE_NAME = XE)
   )
)

Step 6 – Reboot your Computer

Reboot your computer for setting changes to take effect.

Step 7 – Create ODBC Connection

1. Open the ODBC Data Source Administrator from the Windows Control Panel.

   

2. Add a new Data Source and choose the Oracle Instant Client that you installed.

   

3. Enter the appropriate settings for the Oracle Database that you want to connect to and then confirm that the connection is valid by clicking the button [Test Connection].

   

   

Step 8 – Connect with InfoFind

1. In InfoFind open the Database from the menu [Database]->[Open Databases]. On the toolbar at the top of the InfoFind click on the first button in the database toolbar group.

   

2. You will then see the Database Connections form. Create a new connection by clicking the [New Database] button at the bottom of the form. Then click the […] button to build the connection string.

   

3. You will then see the Data Link Properties Dialog. Select “Microsoft OLE DB Provider for ODBC Drivers” and click [Next].

   

4. Fill in the appropriate settings for your database and then click [Test Connection]. If the connection is not successful then please review the steps and settings to make sure that everything is correctly entered. If the connection is successful then you will now be able to use InfoFind to search create reports for you Oracle Database.

   

Step 9 - View and Edit the Database

InfoFind provides excellent support for large Oracle Databases and will allow you to quickly filter through the tables to find what you are looking for; this even applies to databases that have 10’s of thousands of tables. All database data can be exported to user friendly file formats such as Excel, CSV, and Web Pages.