Business Connectivity Service Associations using Oracle Stored Procedures in SharePoint 2010

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

Introduction

In this walkthrough we will demonstrate you how to connect to Oracle Database Server and use stored procedures to create an association between two External Content Types using BCS Meta Man.

We are going to follow on from the ‘Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010’

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 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_all_regions
  return sys_refcursor
  is
  v_regions sys_refcursor;
  begin
  open v_regions for select * from regions;
  return v_regions;
  end get_all_regions;
4. create or replace function get_region_by_id(p_id in number)
  return sys_refcursor
  is
  v_regions sys_refcursor;
  begin
  open v_regions for select * from regions where region_id=p_id;
  return v_regions;
  end get_region_by_id;
5. create or replace function get_countries_by_region_id(p_id in number)
  return sys_refcursor
  is
  v_countries sys_refcursor;
  begin
  open v_countries for select * from countries where region_id=p_id;
  return v_countries;
  end get_countries_by_region_id;

Walkthrough

Using the ‘Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010’ Tutorial create two External Content Types “Country” and “Region” with Finder (Read List operation) and SpecificFinder (Read Item operation) methods only.
You should have the following External Content Types on your Diagram
Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
Select the ‘Association’ toolbox item from the list
With the Association selected, Click and Drag from ‘Region’ to ‘Country’
When you release the ‘Association Configuration’ dialog will show
Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release
The dialog will update with the selected Stored Procedure
Select ‘P_ID’ in the Parameter and ‘REGION_ID’ in the Identifier drop-down list boxes, Click ‘Save’
The diagram will update to show an arrow indicating we have an association between the two External Content Types
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaMan1.Region’ External Content Type, click OK
Click ‘OK’ on the tool pane
The external data provided by Oracle stored procedure is now displayed in the SharePoint Web Part
Next, Add a new ‘Business Data Related List’ Web Part to the page
Configure the Web Part to use the ‘BCSMetaMan1.Country’ External Content Type
The Web Part will display a message saying the following
To do this we can set up the connection using the small arrow from the top right of our Web Part
Once this is done we can click on the arrows next to an item in our ‘Region’ web part and the countries related to this region will display on our ‘Country’ web part