Home    HRIS    Installing the Oracle ODBC Driver

Installing the Oracle ODBC Driver

By / July 28, 2009 / / 0 Comments

The Oracle Instant Client is a simple way to setup ODBC connections to Oracle databases. This is great for PC support people as in the past installing ODBC for Oracle has not been a simple task.

Instant Client also comes with a command line SQL*Plus utility.

Instant Client was released in Oracle 10g but can be used to connect to older Oracle databases to 8.1.7. Instant Client is a free, production ready Oracle tool.

Download Instant Client

Instant Client can be downloaded free from Oracle’s OTN website, you’ll need the:

  • Instant Client Package – Basic (32mb)
  • Instant Client Package – ODBC (700k)
  • Instant Client Package – SQL*Plus (260k) (if you want to use SQL*Plus)

These files come in a winzip format.

Installing Instant Client

Extract the zip file to a directory where you want the Instant Client program to live (say C:\Program Files\Instant Client).

Run the file odbc_install.bat – this updates some registry settings for the ODBC install

Update your PATH environment variable to include the Instant Client directory.

To do this, go to Control Panel, System and click on the Advanced tab.

Click on the Environment variables button and edit the ‘Path’ variable to add the Instant Client directory path (C:\Program Files\Instant Client). Make sure you don’t mess up your other settings in the Path.

The Oracle ODBC drivers have now been installed! Easy.

Set up a tnsnames.ora (optional)

The tnsnames.ora file is a config file used by Oracle software that specifies the connection details of the databases you want to connect to.

If you don’t use a tnsnames.ora file, you’ll need to specify the connection details (server name, port number, database name) every time you connect to the database instead of just specifying the alias name.

To create a tnsnames.ora file create a text file and add:

alien =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 192.168.0.100)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = alien)
    )
  )

Place this file in the Instant Client directory.

Now add a new environment variable TNS_ADMIN (like the Path variable) to tell Instant Client where to look for the file.

Note: If you already have other Oracle software installed on your PC, chances are you have a tnsnames.ora. To use that file add a new environment variable TNS_ADMIN and point this to the location of your existing tnsnames.ora

Configure the ODBC connection

Set up the ODBC connection by going to Control Panel, Administrative Tools, Data Sources (ODBC). Click on the System DSN tab and click Add.

Note: If you only want your Windows login user to see the data source, use the User DSN tab.

If prompted for a driver to use, select the ‘Oracle in instantclient’.

Enter a name and description for the data source, if you are using a tnsnames.ora file the database alias should be available in the TNS Service Name drop down list. Otherwise leave this field blank.

Leave the other settings to default unless required, although if you’re only viewing data it may be wise to check the “Read-Only Connection” checkbox.

Test the Connection

On the ODBC Driver Configuration page (shown above), click the ‘Test Connection’ button. This will bring up the connection screen.

If your TNSNAMES.ora has been set up simply enter your details:

Service name: database alias name
Enter user-name: username
Enter password: password

If you haven’t set up your TNSNAMES.ora the service name will be in the format: servername:port/sid

Where server name is the ip or name of the server running the Oracle database, port is the port number the Oracle listener is running on and sid is the sid name of the database.

For example 192.168.0.50:1521/mydb

If all is good you should get the ‘Test Sucessful’ message returned to you.

Trouble Shooting

System error code 998 – malformed or spaces in the PATH env variable

For more configuration help see the Instant Client Forums.

Connecting to SQL*Plus

To run SQL*Plus, go to the directory where Instant Client Package – SQL*Plus was unzipped and run the application sqlplus.exe. This will bring up the following screen where you will connect to the database.