Office 365: How to Sync Oracle SQL Data with Native SharePoint Lists?

​The Layer2 Cloud Connector for Microsoft Office 365 and SharePoint connects 100+ data sources to the Microsoft Cloud, e.g. ODBC, OLEDB, OData, Microsoft .NET based providers, Files content (Excel, XML, CSV), File shares, SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP, PowerShell - in just minutes without any programming. The step-by-step intro below is about connecting to Oracle data queries.

Fig.: You can connect Oracle data queries with Office 365, SharePoint, Dynamics and 100+ other data sources.

Integrating external Oracle data with native SharePoint lists (e.g. news, contacts, tasks or custom lists) in Office 365, SharePoint Online or SharePoint Server can close some gaps using Microsoft SharePoint, on-premises or in the cloud. Please note: It's not only about displaying external Oracle data - it's about full integration with search, lookups, change notifications, workflows, enterprise metadata, attachments etc. This FAQ gives a step-by-step example on how to do this with Oracle. You will find specific connection strings here.

Prepare Your External Oracle Data Source to Integrate with Office 365

Let's take an example Oracle table, the preloaded DEMO_CUSTOMERS. (Note: this walk-through is done using Oracle Database 11g Express Edition. Your Oracle installation may look different.)

Fig. An example demo table in Oracle which will be used to connect with a native SharePoint list.

Please note:

• You can use existing tables or views, or create direct queries in the Cloud Connector.
• The table should have a primary key defined. If not, you can create one, using a GUID or an auto incrementing counter.

Set Up Your Oracle to SharePoint Connection

​Within the Cloud Connector Connection Manager (a MMC snap-in) you have to setup:

1. The connection (e.g. uni-directional or bi-directional, scheduling etc.),

2. The data source (your Oracle database) and

3. The data destination (For SharePoint on-premises, SharePoint Online or Office 365: The list must be created manually with the appropriate columns before the sync can be done).

You can use auto mapping to connect the fields of both data entities by name or map fields manually.

See here for example connection settings:

Fig. An example Oracle to Office 365 bi-directional connection.

Please note:

• Bi-directional connections require appropriate access rights on database. Use uni-directional connections if you want to push your Oracle data only to the cloud.
• Please enable scheduling after all settings are verified to be working and you have an idea of the replication duration. Duration time depends on bandwidth, performance of source and destination, and number of fields and records in Oracle. After the initial sync, only changes are transferred.

Before running the connection, you have to define the data source and destination.

For connecting to Oracle, you will need to download the Oracle Data Access Components and Tools - .NET and Windows (ODAC) on the Oracle website, and then install it onto the machine hosting the Cloud Connector. Note there is a 32-bit and 64-bit version of the provider. You MUST install the architecture version that matches your Layer2 Cloud Connector installation!

Fig. The installed Oracle .NET Provider is used to connect to the data source with a connection string.

Please note:

• Enter a valid connection string for the Oracle database system. See www.connectionstrings.com to find out more.
• The provider offers the possibility to enter direct queries, thought Oracle does have some conventions differences from MS SQL Server. Please see the Oracle SQL Reference for details.
• Select as few fields as required to increase performance.
• You can use aliases in field names to fit to your SharePoint Online list. For example: select Productname as Title from products
• Use the data preview to check out the result of your query.
• The "primary key" is set automatically, if it is set in the Oracle table.
• Please verify all settings before you save.

Next you have to define your data destination:

Fig. Example data destination with a list "OracleData" in the Office 365 cloud.

Please note:

• Use the Layer2 SharePoint CSOM Provider to connect to SharePoint on-premises, SharePoint Online, or Office 365.
• You have to create the destination list manually with all columns required to host your query data.
• Read more about connection strings to connect to Office 365, SharePoint Online or SharePoint on-premises here. Please note that you can connect to any other type of data destination as well, such as to other SQL databases.
• There is no need to enter a primary key, the provider automatically uses the SharePoint item ID.

As a last step you have to setup the column/field mapping. You can use the auto-mapping feature (by column/field names) or map manually. 

Fig. An example column/field mapping of data source and data destination.

• In this case we have included the CUSTOMER_ID to have a clear reference to the original record.
  That is not a requirement, but advised as best practice.
• Please note the difference in data types. The Layer2 Cloud Connector tries to match as close as possible.

If all settings are verified, please go to connection root and run the connection manually first. Please note, that it could take some time to transfer all columns if you have a lot of data. You can take a look in your SharePoint list to see the progress.

Manage Your Oracle SQL Data in the Office 365 SharePoint List

​Now take a look at your list. All items are in there, after running the connection. You can hide the Source ID from you, if not required.

Fig. Example data destination in Microsoft SharePoint 2013/Office 365.

Please note:

• Changed items are transferred with the next scheduled update. That means, you can setup change notifications per-email or workflows in the cloud without any restrictions.
• You can add additional "SharePoint only" data, e.g. attachments or managed metadata. It will be kept while items are updated (via primary key).
• You can do anything you can generally do with a native SharePoint list - without any restrictions (e.g. search).
• Don't forget to setup the scheduled background update to keep data current in connection settings, for example with scheduled updates every hour or daily.

READY TO GO NEXT STEPS?