How to connect a native SharePoint list directly with an Oracle database query as external data source?

You can connect a native SharePoint list (e.g. contacts, news, tasks, events or custom) directly with an Oracle database query as external data source with the following steps:

 

Install Oracle Data Provider for SharePoint:

 

Please find the Oracle Data Provider for .NET (64-Bit) download that is required to connect from SharePoint, install and setup.

 

Create a new list and enter list settings:

 

Please click "Connect to External Data Source" to connect to Oracle. If this link is not available, the Business Data List Connector is not installed on SharePoint or not activated in the current SharePoint Site Collection as a Site Collection feature.

 

 

 

Fig.: To connect to Oracle simply enter list settings and click "Connect to external data source".

 

Enter Oracle connection data:

 

To connect your SharePoint list directly to Oracle enter the connection data as shown below.

 

 

 

Fig: Enter connection data to connect the current list to Oracle. 

 

 

Select the Oracle Connection Provider "Oracle Data Provider for .NET":

 

If its not shown in the list, please install and configure as noted above.

 

Enter the Oracle Connection String for SharePoint:

 

Here are some sample connection strings for Oracle databases.

 

via .NET Provider selected (preferred):

 

Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;

 

Or with integrated security:

 

Data Source=MyOracleDB;Integrated Security=yes;

Please take care about the double hop issue in case of integrated security. Also note that the users and the SharePoint timer service account must have access to your external data in this case. 

 

via OLEDB Provider  selected (not preferred)

 

To open a connection to Oracle database with standard security use this one:

 

"Provider=MSDAORA; Data Source=Your_Oracle_Database; UserId=Your_Username; Password=Your_Password;"

 

Or

 

"Provider= OraOLEDB.Oracle; Your_Oracle_Database; UserId=Your_Username; Password=Your_Password;"

 

To open a trusted connection to Oracle database please use this connsction string:

 

"Provider= OraOLEDB.Oracle; DataSource=Your_Oracle_Database; OSAuthent=1;"

 

Enter your Oracle query to connect to SharePoint:

You can use select statements and primary key(s) as usual with Oracle, e.g. select * from myTable.

 

Enter your Oracle table primary key(s) to savely update the SharePoint list:

 

If primary keys are available, you can use list alerts and workflows to start business actions in SharePoint, when external Oracle data is changed in database.

Now you are ready to create the list structure automatically. You can modify column titles and data types at any time. Please check mapping. You can also enable background update or optionally write-back. Full CRUD (create, update, delete) is provided.

 

Fig.: You can update your list on demand using the ribbon button or URL. Alternatively you can update automatically using the background update (via timer job).

Known issues and workarounds with Oracle connected to SharePoint

• System.Data.OracleClient requires Oracle client software version 8.1.7 or greater
  
  Quick resolution:
   
  1.Give full permission to AUTHENTICATED USERS in following folders.
  a) ORACLE_HOME
  b) Program Files\ORACLE
    
  2.Check your PATH. You might have installed different clients in your system and your .NET application is pointing to a home with inappoperiate client. What your .NET application should load is OCI.DLL with File version more than 8.1.7.

 

• The specified DSN contains an architecture mismatch between the Driver and Application
  
  You have installed the 32-Bit client software on a 64-bit system, but 64-bit client is required for 64-bit SharePoint to have access, e.g. via ODBC.

Do you want more TechChaitu Updates ?

Enter your email address: