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,
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.
- Bi-directional connections require appropriate access rights on database.
Use uni-directional connections if you want to push your Oracle data only to the
- 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
Before running the connection, you have to define the data source and
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
Fig. The installed Oracle .NET
Provider is used to connect to the data source with a connection string.
- 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
- 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.
- 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.
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.