Connect to Oracle using the Database connector in Jitterbit Integration Studio

This page details how to connect to Oracle database using the Database connector.

You can connect to an Oracle database using either a JDBC or ODBC driver. Version 19.7 of Oracle's JDBC driver is included with cloud and private agents. The JDBC driver is recommended for additional features, such as being able to provide a manual query, that are not available through an ODBC connection. This section describes how to configure an Oracle database within Integration Studio using an ODBC driver on private agents.

Install the Oracle client on the Jitterbit private agent

Before you can establish a connection to Oracle through ODBC, you must have the Oracle Client (including the Oracle ODBC driver) installed on the 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 for details.

Configure and test the Oracle client

After 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 the 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 private agent, you should test the server to ensure that Harmony has connectivity to the database. One way to do this is to connect to the server through SQL*Plus. If this is successful, the Oracle Client should operate correctly with Harmony.

In addition, some versions of the Oracle driver (not the Microsoft Oracle driver) may require one or more 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 depends 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 Database connection

After the Oracle Client is configured and tested, specify these inputs during configuration of the Database connection:

Driver Type: Select ODBC.
Driver: Select the Oracle driver. If you do not see your driver in the list, see Add your driver to the Jitterbit agent config file below.
Use Connection String: Under Optional Settings, select this checkbox. You must construct the connection string manually, as Oracle references the database based on the setting in tnsnames.ora.
Connection String: Under Optional Settings, enter the 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];
```
Tip

Additional Oracle connection string examples are available at The Connection Strings Reference.

Add your driver to the private agent config file

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

Once the driver information is added as described below, while selecting your driver within Integration Studio you should see your driver appear in the list of ODBC drivers. If the steps below are not successful, see Install additional ODBC or JDBC drivers for details 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 details on remote agent configuration, see the section on Agents under Agents.

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 dialog, 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 private 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. See Restart a Windows private agent or Restart a Linux private agent for more information.

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 details, see Edit 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'='\"'
```
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. See Restart a Windows private agent or Restart a Linux private agent for more information.

WHERE clause examples

These WHERE clause examples are provided for reference purposes when configuring a Database query activity:

WHERE
    product_name = 'Kingston';

WHERE
    list_price > 500;

WHERE
    list_price > 500
    AND category_id = 4;

WHERE
    list_price BETWEEN 650 AND 680

WHERE
    category_id IN(1, 4)

WHERE
    product_name LIKE 'Asus%'

WHERE business_seats_taken IS NULL
    OR business_seats_taken = 0

WHERE EMP_ACT.EMPNO = EMPLOYEE.EMPNO

WHERE THIS_EMP.JOB = 'SALESREP'
       AND THIS_EMP.WORKDEPT = DINFO.DEPTNO

For additional examples, refer to Oracle Basics and the Oracle article WHERE clause.