Database Target Connecting to Oracle via ODBC

This page describes how to configure an Oracle database as a source or target within Jitterbit Studio using an ODBC driver. See Database for information on the types of authentication supported for various systems.

NOTE: You can also connect to an Oracle database using a JDBC driver. The JDBC driver is recommended for additional features, such as manual creation of SQL scripts, that are not available via an ODBC connection.

Install the Oracle Client on the Jitterbit Private Agent

Before you can establish a connection to Oracle via ODBC, you must have the Oracle Client (including the Oracle ODBC driver) installed on your Jitterbit Private Agent. If you are using multiple Private Agents within an Agent Group, the Oracle Client must be installed on all Agents in the group. See Agents > Agents and Agents > Agent Groups for more information.

Jitterbit currently supports 32-bit drivers. Currently, the Oracle ODBC driver installed on all Agents in the Agent Group must be 32-bit. If you are using a 64-bit Oracle Client and ODBC driver, you will experience problems connecting to your Oracle database. To resolve these issues please install the 32-bit version.

Configure and Test the Oracle Client

Once you have installed the Oracle Client, configure the tnsnames.ora file to reference the database you are planning to use. This is part of the standard Oracle Client setup procedure. For additional information see Tnsnames.org - Oracle FAQ or refer to the Oracle documentation for the version you are using.

After the Oracle Client is set up on the Jitterbit Agent, you should test the server to ensure that Jitterbit has connectivity to the database. One way to do this is to connect to the server via SQL*Plus. If this is successful, the Oracle Client should operate correctly with Jitterbit.

In addition, some versions of the Oracle driver (not the Microsoft Oracle driver) may require one or more of the following environment variables within the operating system to be set. For example:

ORACLE_HOME=/usr/local/oracle/10.2.0
TNS_ADMIN=/usr/local/oracle/10.2.0/network/admin
TWO_TASK=//dbdpmr:1555/pmrd1

On Linux, you may also need to add the Oracle library directory to your path (e.g. LD_LIBRARY_PATH). Add these to the /etc/sysconfig/jitterbit file. Some examples are shown below, but the exact path will depend on your Oracle Client installation.

LD_LIBRARY_PATH=/usr/local/oracle/lib
export ORACLE_HOME=/usr/local/oracle/10.2.0
export TNS_ADMIN=/usr/local/oracle/10.2.0/network/admin
export TWO_TASK=//dbdpmr:1555/pmrd1

Configure the Oracle Connection in Jitterbit Studio

Follow the steps below to configure your Oracle connection as a source or target in Jitterbit Studio.

Go to Jitterbit Studio and set up your source or target as normal, choosing Database as the type.
Select your Driver by clicking the Select button to display a prompt to select available drivers. In the prompt, make sure the checkbox for ODBC is checked, then select your driver from the list and click OK. If you do not see your driver in the list, see Add Your Driver to the Jitterbit Agent Config File below.
Click on Options to expand additional settings.
1. Check the box for Construct connection string manually, as Oracle references the database based on the setting in tnsnames.ora.
2. Enter a manual connection string for your specific Oracle database. For example:
  Oracle in OraHome92
```
Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;
```
  Oracle in OraClient11g_home1
```
DRIVER={Oracle in OraClient11g_home1};SERVER=[server SID];UID=[userid];
PWD=[password];DBQ=[server SID];
```
  NOTE: Additional Oracle connection string examples are available at https://www.connectionstrings.com/.

Add Your Driver to the Jitterbit Agent Config File

Because of the many variations on the Oracle driver name, you may need to add your driver information to the Jitterbit configuration file.

Once the driver information is added as described below, within Jitterbit Studio while selecting your driver, click the Refresh button and you should see your driver appear in the list of ODBC drivers. If the below steps are not successful, you can also reference Installing Additional ODBC or JDBC Drivers for more detailed instructions regarding driver installation.

Remote Agent Configuration

If you have remote Agent configuration enabled through the Management Console, you can add your driver information under the Db Drivers tab as follows. For more information on remote Agent configuration see View or Edit Jitterbit Conf under Agents > Agent Groups.

Within the Management Console, go to Agents > Agent Groups.
Within the table, find your Agent Group and use the Action dropdown to select Jitterbit Conf.
In the popup, select the tab Db Drivers, then click Edit in the upper right. Find the following example entry for a specific Oracle version, and replace the provided sample information with that for your specific Oracle ODBC driver. When complete, click Submit to save the driver information to the Jitterbit Agent configuration file.
```
#Oracle in OraClient10g_home2
'Oracle in OraClient10g_home2 Quote Begin'='\"'
'Oracle in OraClient10g_home2 Quote End'='\"'
```

All Jitterbit services must be restarted in order for the new configuration to take effect.

On the machine where each Private Agent is installed, first stop the services, then restart them.

Windows

Linux

From the Start Menu:

Stop Jitterbit Services
Start Jitterbit Services

From the Jitterbit Agent installation directory:

> StopServices.bat
> StartServices.bat

From a prompt:

$ sudo jitterbit stop $ sudo jitterbit start

Manual Agent Configuration

If you are using manual Agent configuration, you can add your driver information under the [DbDrivers] section of the jitterbit.conf file located on each Private Agent. For more information see Editing the Configuration File - jitterbit.conf.

Open jitterbit.conf in a text editor and scroll to the [DbDrivers] section.
Find the following example entry for a specific Oracle version, and replace the provided sample information with that for your specific Oracle ODBC driver. When complete, save the jitterbit.conf file.
```
#Oracle in OraClient10g_home2
'Oracle in OraClient10g_home2 Quote Begin'='\"'
'Oracle in OraClient10g_home2 Quote End'='\"'
```