Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010

By admin BCS BCS Meta Man Business Connectivity Services 5th September 2011

Introduction

In this walkthrough we will demonstrate how to integrate Oracle stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

Microsoft SharePoint Server 2010
Microsoft Visual Studio 2010 Professional or higher
BCS Meta Man
Oracle clients (This FAQ describes what should be installed)
Oracle stored procedures for CRUD operations such as:
1. create or replace function get_all_countries
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries;
  return v_countries;
  end get_all_countries;
2. create or replace function get_country_by_id(p_id in char)
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries where country_id=p_id;
  return v_countries;
  end get_country_by_id;
3. create or replace function get_country_ids
  return sys_refcursor
  is
  v_country_ids sys_refcursor;
  begin
  open v_country_ids for select country_id from countries;
  return v_country_ids;
  end get_country_ids;
4. create or replace procedure create_country(p_id in char, p_name in varchar2, p_region in number)
  is
  begin
  insert into countries values(p_id, p_name, p_region);
  end create_country;
5. create or replace procedure update_country(p_id in char, p_name in varchar2, p_region in number)
  is
  begin
  update countries set country_name=p_name, region_id=p_region where country_id=p_id;
  end update_country;
6. create or replace procedure delete_country(p_id in char)
  is
  begin
  delete from countries where country_id=p_id;
  end delete_country;

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e OracleStoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter Oracle ODBC connection string (for example it can be “Driver={Oracle in OraClient11g_home1};Dbq=XE_TAURUS;Uid=hr;Pwd=hr;Trusted_Connection=yes;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.
The Data Source Explorer will now be populated with your Oracle data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Country”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – P_ID and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created OracleStoredProceduresBCSMetaManDemo.Country External Content Type, click “OK”. Click “Create” button then.
The external data provided by Oracle stored procedure is now displayed in the SharePoint External List
You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.
You will be presented with the View Item dialog
Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
This will bring up the “New Item” dialog where you can add a new Country. We will enter information about new Country and click “Save”
This will insert a new row directly to your Oracle database