Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Excerpt

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.

Info
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:

Code Block
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.

Code Block
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.

  1. Go to Jitterbit Studio and set up your source or target as normal, choosing Database as the type.
  2. 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.
  3. 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: 

      Code Block
      titleOracle in OraHome92
      Driver={Oracle in OraHome92};Dbq=myTNSServiceName;Uid=myUsername;Pwd=myPassword;
      Code Block
      titleOracle in OraClient11g_home1
      DRIVER={Oracle in OraClient11g_home1};SERVER=[server SID];UID=[userid];
      PWD=[password];DBQ=[server SID];
      Tip
      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.

  1. Within the Management Console, go to Agents > Agent Groups.
  2. Within the table, find your Agent Group and use the Action dropdown to select Jitterbit Conf.
  3. 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.

    Code Block
    #Oracle in OraClient10g_home2
    'Oracle in OraClient10g_home2 Quote Begin'='\"'
    'Oracle in OraClient10g_home2 Quote End'='\"'
  4. All Jitterbit services must be restarted in order for the new configuration to take effect.
    Multiexcerpt include
    MultiExcerptNameRestart-Services
    PageWithExcerptEditing the Configuration File - jitterbit.conf

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.

  1. Open jitterbit.conf in a text editor and scroll to the [DbDrivers] section.
  2. 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.

    Code Block
    #Oracle in OraClient10g_home2
    'Oracle in OraClient10g_home2 Quote Begin'='\"'
    'Oracle in OraClient10g_home2 Quote End'='\"'
  3. All Jitterbit services must be restarted in order for the new configuration to take effect.
    Multiexcerpt include
    MultiExcerptNameRestart-Services
    PageWithExcerptEditing the Configuration File - jitterbit.conf

...


...

Easy Heading Macro
headingIndent0
expandOptiondisable-expand-collapse
navigationTitleOn This Page

...

wrapNavigationText

...

Panel
borderColor#FF7C4C
titleColor#FFFFFF
titleBGColor#FF7C4C
titleRelated Articles
Content by Label
showLabelsfalse
max1000
showSpacefalse
sorttitle
cqllabel in ("database","oracle","odbc") and space = "DOC"

...

borderColor#00B886
titleColor#FFFFFF
titleBGColor#00B886
titleRelated Topics

...

true

...

maxLevel3
minLevel2
indent20px

navigationExpandOptionexpand-all-by-default

HideElements
metastrue

...