Oracle and SharePoint

By

BCS Meta Man enables you to create a BDC Model file for Oracle and other External Data Sources within just a few minutes of configuration. In order for Business Connectivity Services to work with Oracle, you do need to have some perquisites in place, such as those below:

  • Oracle Client Installed
  • SharePoint 2013 Secure Store Configured
  • BCS Meta Man Installed.

Installing the Oracle Client

Firstly, the Oracle Client must be installed on both the computer you wish to use BCS Meta Man on and the SharePoint server that you want to connect to Oracle with. Please ensure you choose the ‘Administrator’ installation type, as it contains everything you need for both SharePoint and BCS Meta Man to connect to your database.

You will most likely be able to leave the subsequent options as their default values. The installer will then proceed to perform some prerequisite checks that will need to pass before you can continue.

Once the checks have passed you can click Finish and Oracle Client will install. Assuming all is well, you are free to move on to the next step.

clip_image004

Oracle Client Installation

 

Configuring Secure Store for SharePoint and Oracle

If you do not already have them, create a new Business Data Connectivity Service Application and a new Secure Store Service Application. Ensure they are configured correctly and use the same managed account.

Once this is done, click on to your Secure Store Service Application and click Manage in the ribbon. If it is a new Service Application, you will need to click Generate New Key in the ribbon and choose a password. This password is used to encrypt the Secure Store database, so make sure you keep it safe.

Click New in the Manage Target Applications section of the ribbon. In this section you can choose an arbitrary Target Application ID and Display Name that you will use to identify this Target Application. I chose to use SharePoint Oracle for both. Choose Group as the Target Application Type. This means that you can map many users to a single credential for connections to Oracle. Leave the Target Application Page URL as default. Once you have filled out all this information click Next.

On the next page you will have options for Field Names and Field Types. You can name the fields whatever you choose, but set the first Field to Field Type ‘User Name’ and the second to ‘Password’.

 

clip_image006

Creating a New Secure Store Target Application

Next you can set Administrators and Members for the Target Application. Members are SharePoint users who will be mapped to the Oracle credentials, so anyone in this box will be able to authenticate. Add the users you would like to have and click OK.

clip_image008

Setting the Secure Store Credentials

Finally, click the name of the new Target Application and enter your logon details for Oracle. In this example I have used the SYSTEM account, though this is not best practice. These logon details can now be impersonated by all of the users you entered into the Members field during the Target Application configuration.

Creating a SharePoint External Content Type for Oracle

Open up BCS Meta Man (if you don’t have it already, you can get a free trial HERE). In this guide we are using the Windows edition, but the Visual Studio edition will also work fine for SharePoint 2010. Click the Oracle data source tab on the left and enter credentials for the Oracle Database. Ensure that the ‘use secure store’ checkbox is checked – this will allow you to use Secure Store in SharePoint once your External Content Type is generated. The exact settings you need here will vary depending on your Oracle installation.

clip_image010

Connecting to the Oracle database with BCS Meta Man

On the next screen you can choose tables to add to your model diagram, create associations between tables, and more – but for this guide I will only add one table. Make sure you click the Settings button in the top-right hand corner, to ensure that BCS Meta Man is configured correctly. You can also name your External Content Type here. Once you have finished setting up your model, click either Generate or Deploy. Generate will create your BDCM file on the desktop, and Deploy will both create it and automatically add it to SharePoint if you have the right permissions.

clip_image012

Building the methods for the Oracle External Content Type

Oracle SharePoint Connections for Business Connectivity Services

If you chose to Generate your BDC Model rather than Deploy, you will have to import it to the Business Data Connectivity Service Application before you can use it. Go to Central Administration and click ‘Manage service applications’ under Application Management. Click on to your BDC Service Application, and click Import in the ribbon. On the next page you can browse for your BDCM file and select it. You can leave the other settings as default unless you know that you need to change them.

clip_image014

Importing the BDC Model File

Now you can head to the site where you would like to make your External List. Click on Site Contents on the left and then Add An App. Create a new External List app, give it a name, and type in the name of the External Content Type. You can choose this in BCS Meta Man’s settings window, but it will also be listed in the configuration for your BDC Service Application. Finally, click Create and SharePoint will make the External List. It should now show in your Site Contents with your other Apps.

 

clip_image015

An Oracle External List

If you require any assistance with BCS Meta Man or configuring Oracle Client, please let us know via the Support tab.

<Nathan/>