Connecting to Oracle

These notes were written for Oracle Version 9 and Version 10g. The names of the Oracle products, and how they are packaged, can be different for different releases of Oracle.

Prerequsite Software

In order to connect to an Oracle database, you need to install the Oracle Client or Instant Client on your PC.

In addition to this, you need an Oracle ODBC Driver. You will have a choice of:

• the Microsoft-supplied ODBC Driver for Oracle. This comes as part of the standard Windows install.
• the Oracle-supplied ODBC Driver. This comes as an optional part of the Oracle Client install. When installing the Oracle Client you need to do a Custom install; the ODBC Driver will be part of the Oracle Programmer component.
• there are a number of third-party ODBC Drivers, such as Devart (https://www.devart.com/odbc/oracle/). We haven't used this ODBC Driver so cannot comment on its capabilities.

While AQT works with both ODBC Drivers, we strongly recommend that you use the Oracle ODBC Driver. The Microsoft ODBC is limited in functionality. Principally:

• Large objects (BLOBs, CLOBs) are not supported
• Unicode data cannot be displayed
• A query using the WITH clause will not display any data

Oracle Net Configuration

To configure a connection between your PC and an Oracle server you need to run the Oracle Net Configuration. Once you start this, select Local Net Service Name configuration then Add. You will be asked the following information:

• Service Name. This is the global database name for your database. Your DBA will know what this is.
• Communication Protocol. Use TCP.
• Host Name. Enter the IP address or host name of the machine on which the Oracle server resides. Use localhost if the database resides on your own machine.
• Port Number. The default port (1521) will generally be the correct value. However it is possible that your DBA has configured Oracle to use a different port; check with him/her.
• Net Service Name. This is the name you give for this connection. A good standard is to give it the same name as the Service Name.

Once you have configured this connection, you will be able to access your Oracle database using SQL*PLUS.

ODBC Driver Configuration

In order to access your Oracle database using AQT, you will need to configure an ODBC Datasource for your database. The general process for doing this is described in Configuring a Database Connection.

To configure this, specify:

• Data Source Name. The name you give this ODBC datasource.
• Description. Not used. Leave blank.
• User Name. Not used. Leave blank.
• Server (Microsoft ODBC Driver). The Net Service Name as configured in Oracle Net Configuration. This is the name you use when you sign onto the database using SQL*PLUS.
• TNS Service Name (Oracle ODBC Driver). As with Server above.

With the Oracle ODBC Driver, it is a good idea to click on Test Connection to check that the connection has been set up correctly.

In addition (for the Oracle ODBC Driver)

• on the Oracle tab, ensure that Enable LOBs is checked. If this is not checked, you will be unable to insert LOB columns successfully.

Installing the Oracle Instant Client

To install this, you need to download two packages:

• Client Package - Basic
• Client Package - ODBC

We found that the "Client Package - Basic Lite" did not work for us.

Follow the instructions to install the products, and to install the ODBC Driver. You will also need to:

• change your PATH environment variable to include the directory containing SQORA32.DLL
• set up a tnsnames.ora file. This can be placed either in the same directory as the Instant Client or some other directory. The Instant Client provides no means to configure the tsnames.ora file (which contains the Oracle network names). The easiest way to create tnsnames.ora is to set it up on a machine that has the full client then copy it across.
• set environment variable TNS_ADMIN to the directory containing tnsnames.ora

When configuring the ODBC Driver (as per the ODBC Driver Configration discussion above), you may get error The setup routines cannot be loaded due to system error code 126. The cause of this appears to be due to a couple of dll files missing from the install. These are:

• mfc71.dll
• msvcr71.dll

You will need to get these dlls from a machine which has the full client installed on it (they will be in c:\Windows\System32). Copy them either to your c:\Windows\System32 or your Instant Client directory.

Diagnosing Connection Problems

See the section Oracle - Diagnosing Connection Problems