Using ODBC to sync to/from an Oracle database

Syncs to/from Oracle are supported via the ODBC Data Source templates. To utilize the ODBC Data Source for syncing to Oracle, you must:

• Install the Oracle Client on the UnitySync server.
• Setup an ODBC Datasource for your Oracle database.

See Syncing to a Destination ODBC Datasource.
See Importing from a source ODBC Datasource.

Important Notes about a Destination Oracle Database

The Destination Oracle Database must exist and the table to write to must also exist and have all columns defined. UnitySync does not create the database, table or columns.

Custom Mapping is required. See how do I create a custom object map for details on creating a custom object map file. Also see the Custom Mapping: ODBC topic in the Administrator’s Guide for details specific to an ODBC/Oracle destination.

It is highly recommended that the field specified as the index field be configured in the Destination ODBC database as “Duplicates Allowed = No” otherwise duplicate Source data may result in duplicate database entries. Also, UnitySync Recovery Procedure will not process successfully unless “Duplicates Allowed = No” is set.

When syncing to a Destination Oracle Database, if your oracle Destination index attribute is not modifiable be sure to add the following attribute to the connection’s Raw Config. This allows the sync to create objects and set the Destination Index attribute on the initial add, but will not attempt to write that attribute on subsequent modifies. To do this, click the Raw Config button on the Custom tab and add the following line:
no-mod-attribs=OracleDestIndexAttributeName

Important Notes about a Source Oracle Database

To query a Source Oracle Database table via your ODBC datasource, you will specify a simple SQL query against the database. The results of this query will produce the data that will be synced. You must provide a valid SQL statement to produce the necessary dataset. Writing your own SQL statement provides you with the ability to be as broad or specific with the records you sync.

For example, “Select * from employees” would provide you a dataset of all employees in your database. “Select * from employees where country = ‘US’ ” would be a more specific and perhaps smaller dataset to sync.

If you need more than a simple query, it is recommended that you set up the desired SQL query as a View in Oracle. That way you can test/validate the complex query on the server and just let UnitySync pull all of the records from the view.