Best Practices from Oracle Development's A‑Team

Loading Data from Generic JDBC Sources into ADW

* This blog was last tested on Oracle Analytics Cloud Data Sync (2.6.1) + ADW 18c by Jay Pearson (Oracle A-Team) * 

For other A-Team articles about OAC and Data Sync, click here


This functionality has been available since Version 2.2 of the Data Sync tool, released September 2016, when the ability to connect to a number of different data sources via JDBC was added to the product.

Setting up these Data Sources in Data Sync varies by the source.  Rather than create a single article to cover them all, 3 have been written.  Select the appropriate article to learn more about the steps to set up that Data Source:

This article will cover a number of the JDBC drivers that come pre-loaded with the Data Sync tool (Greenplum, Hive, Impala, Mongodb. Postgresql. Redshift, Sybase) as well as how to set up connections to different data sources where for which a JDBC driver is available.


Data Source Article With Details
Greenplum, Hive, Impala, Mongodb. Postgresql. Redshift, Sybase, and directions for other JDBC data sources where the driver is available this article
Service Cloud / RightNow link to article
SalesForce link to article


Downloading Latest Version of Data Sync Tool

Be sure to download and install the latest version of the Data Sync Tool from Oracle Analytics Downloads.

For instructions on how to configure ADW as the target, see this article.

For general instructions on configuring Data Sync, see this article.

For further information on the Data Sync Tool, upgrade instructions, OAC Data Sync readme (Data Sync release notes and known issues), and OAC Data Sync Documentation (Data Sync install and user guide) can be found here.


Setting up JDBC Connection

The following JDBC drivers are included with version 2.6 of the Data Sync tool.  To connect to any of these data sources, no additional JDBC drivers need to be downloaded, and the connection can be set up immediately.

For Databases not in this list - see the section in this document 'Setting up Connection for a Different JDBC Driver'


Database:     GreenPlum

Driver:           com.oracle.bi.jdbc.greenplum.GreenplumDriver

URL:              jdbc:oracle:greenplum://$hostname:$port;DatabaseName=$databasename;


Database:     Hive

Driver:           com.oracle.bi.jdbc.hive.HiveDriver

URL:              jdbc:oracle:hive://$hostname:$port;DatabaseName=$databasename;


Database:     Impala

Driver:           com.oracle.bi.jdbc.impala.ImpalaDriver

URL:              jdbc:oracle:impala://hostname:$port;DatabaseName=databasename;


Database:     Mongodb

Driver:           com.oracle.bi.jdbc.mongodb.MongoDBDriver

URL:              jdbc:oracle:mongodb://$hostname:$port;DatabaseName=$databasename;


Database:     Postgres

Driver:           com.oracle.bi.jdbc.postgresql.PostgreSQLDriver

URL:              jdbc:oracle:postgresql://$hostname:$port;DatabaseName=$databasename;


Database:     Redshift

Driver:           com.oracle.bi.jdbc.redshift.RedShiftDriver

URL:              jdbc:oracle:redshift://REDSHIFT_ENDPOINT:$port;DatabaseName=$databasename;


Database:     SalesForce

Driver:           com.oracle.bi.jdbc.sforce.SForceDriver

URL:              jdbc:oracle:sforce://login.salesforce.com;SecurityToken=xxxxxxxxxxxxxxxxxxxxxxx


Database:     Sybase

Driver:           com.oracle.bi.jdbc.sybase.SybaseDriver

URL:              jdbc:oracle:sybase://$hostname:$port;DatabaseName=$databasename;


1. Create a Connection

Within the 'Connections' section, select 'New'


In the detail box

a, Enter a Name for the connection

b. Select 'Generic JDBC' as the Connection Type

c. If the database requires a Username and Password, enter those in the 'User' and 'Password' boxes

d. In the 'URL' box, enter the connection details for the database.  Use the format from the list above, replacing the $host, $port, $database values as appropriate.

e, In the 'JDBC Driver' box, cut and paste the text from the relevant 'Driver' column.

2. Test the connection and confirm it works

3. Define the Data Schema

It is good practice to pick the source Database Schema that contains the data to be imported.

In the details of the connection - in this case a Greenplum connection - select the schema box to bring up a list of available schemas and chose the one that contains the data.  If multiple schemas are needed, create a new connection for each one and name appropriately.


4. Enter Source Database specific record separators

This only applies in some cases where some of the field names in the source database contain spaces.  This can cause a problem when Data Sync generates the select statement.  In these cases database specific separators need to be entered.  If this applies:

a. Select the Connection, and the 'Advanced Properties' and enter a value, or values into 'Enclose object names'

If, for instance, a double quote character needs to enclose the start and end of the field name (in the case of SalesForce), then just entering a single " in the value box will work. If the character is different to start and end a field, for instance if a field needs to be surrounded by [ ] (in the case of MS Access), then enter the characters separated by a comma, so [,]


Setting up Connection for a Different JDBC Driver

Data Sync has the ability to work with many other generic JDBC drivers.  This section describes how to set up such a connection.  In this case the example of MS Access is used.

1. Close the Data Sync tool, and 'Exit' out completely from the option in the task bar.


2. Locate the JDBC driver for the data source and download to the environment where Data Sync in installed.  In this example, the UCanAccess driver, available here, was downloaded.

3. Create a new folder in the /lib directory within the Data Sync folder and save the JDBC driver file(s) in there.  Data Sync will read all files within the /lib directory, so adding folders helps organize the new drivers.  In this case a new folder called 'Access' was created, and the UCanAccess driver and support files copied there.


4. Open Data Sync and create a new connection


5. Select the 'Connection Type' to be  Generic JDBC


6. Use the documentation for the JDBC driver to figure out the JDBC Driver and URL details.  In this case the format for the JDBC driver is


And the JDBC URL:


7. Work through steps 2-4 from the 'Setting up JDBC Connection' section above.

If issues are encountered with the connection, do due diligence testing to try and resolve whether the issue is with the driver or Data Sync.  If possible, locate and try a different driver for the same Database Source.  While many generic JDBC drivers should work, there are some that may not.  At the current time, the only drivers the tool is certified to work with are the ones that are packaged with it.  If it's ascertained that the issue is with the Data Sync tool, and you have access to an oracle employee, have them open a bug against product '10432' and the 'DATASYNC' component, otherwise create a low priority SR. In both cases providing details of the driver and error.  The DEV team can then look at the issue, and possibly add support in the next release of the Data Sync tool.


This article walked through the steps to configure the Data Sync tool to be able to connect and extract data via generic JDBC connections to data sources.

For other A-Team articles about OAC and Data Sync, click here

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha