Search

Oracle Connector Setup

Version 22.0.8473

Oracle Connector Setup

The Oracle connector allows you to integrate Oracle into your data flow by pushing or pulling data from Oracle. Follow the steps below to connect CData Arc to Oracle.

Configuring Dependencies

To use Oracle in your flows, you must first install and configure the dependencies that Oracle requires. Follow the directions below for your version of Arc.

Windows Edition

  1. Open the Arc installation directory (by default, C:\Program Files\CData\CData Arc).

  2. Open the www folder.

  3. Open the Web.Config file in a text or code editor. This is an XML-formatted file.

  4. Navigate to the element <configuration>/<system.data>/<DbProviderFactories>.

  5. At the bottom of this element, add the following text:

     <remove invariant="Oracle.ManagedDataAccess.Client" />
 <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess" />

  6. Save the Web.Config file and close it.

  7. Download the Oracle ODAC .dll files from the Oracle website. Choose the option titled ODP.NET_Managed_ODACxxxx.zip where the xxxx represents the version in the file name.

    Note: Access to this download requires an Oracle account.

  8. Extract the files to a folder. Copy the files outlined in the table below to their corresponding subfolders in the Arc installation directory.

File Name Relative Path to Copy to
Oracle.ManagedDataAccess.dll \bin
Oracle.ManagedDataAccessDTC.dll (x64 version) \bin\x64
Oracle.ManagedDataAccessIOP.dll (x64 version) \bin\x64
Oracle.ManagedDataAccessDTC.dll (x86 version) \bin\x86
Oracle.ManagedDataAccessIOP.dll (x86 version) \bin\x86

Java Edition

  1. Download the Oracle JDBC .jar file from the Oracle website.

    • If you are using the Arc embedded Jetty server, select the latest release of ojdbc11.jar.

    • If you are using another hosting option, select the latest release of either ojdbc11.jar or ojdbc8.jar, depending on the version of JDK your hosting server uses.

  2. Move the file to the appropriate location:

    • If you are using the embedded Jetty server, move the file to the lib folder in the Arc installation directory ([Arc]/lib). If this folder does not exist, create it.

    • If you are using another server, move the file to the lib folder for the server. If this folder does not exist, create it.

Establish a Connection

To allow Arc to use data from Oracle, you must first establish a connection to Oracle. There are two ways to establish this connection:

  • Add a Oracle connector to your flow. Then, in the settings pane, click Create next to the Connection drop-down list.
  • Open the Arc Settings page, then open the Connections tab. Click Add, select Oracle, and click Next.

Note:

  • The login process is only required the first time the connection is created.
  • Connections to Oracle can be re-used across multiple Oracle connectors.

Enter Connection Settings

After opening a new connection dialogue, follow these steps:

  1. Provide the requested information:

    • Name—the static name of the connection. Set this as desired.

    • Type—this is always set to Oracle.

    • Connection Type—the connection type (SID or Service Name) to use.

    • User—the username to use for logging in.

    • Password—the password for the user entered above.

    • Server—the address of the Oracle server you want to connect to.

    • Port—the port to use when connecting to the server.

    • SID/Service—the SID or Service (depending on Connection Type chosen) to use when connecting.

    • Other—other authentication information if needed.

  2. If needed, click Advanced to open the drop-down menu of advanced connection settings. These should not be needed in most cases.

  3. Click Test Connection to ensure that Arc can connect to Oracle with the provided information. If an error occurs, check all fields and try again.

  4. Click Add Connection to finalize the connection.

  5. In the Connection drop-down list of the connector configuration pane, select the newly-created connection.

  6. Click Save Changes.

Select an Action

After establishing a connection to Oracle, you must choose the action that the Oracle connector will perform. The table below outlines each action and where it belongs in an Arc flow.

Action Description Position in Flow
Upsert Inserts or updates Oracle data. By default, if a record already exists in Oracle, an update is performed on the existing data in Oracle using the values provided from the input. End
Lookup Retrieves a value from Oracle and inserts that value into an already-existing Arc message in the flow.
The Lookup Query determines what value the connector will retrieve from Oracle. It should be formatted as a SQL query against the Oracle tables.		 Middle
Select Retrieves data from Oracle and brings it into Arc.
You can use the Filter panel to add filters to the Select. These filters function similarly to WHERE clauses in SQL.		 Beginning
Execute Stored Procedures Treats data coming into the connector as input for a stored procedure, and then passes the result down the flow.
You can click the Show Sample Data button to provide sample inputs to the selected Stored Procedure and preview the results.		 Middle