BCS filters and Oracle Stored Procedures with Parameters in SharePoint 2010

Introduction

In this tutorial we will show you how to connect to a Oracle Database Server using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our Oracle Stored Procedure needs to have parameters defined.

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 procedure for such as:
    1. create or replace function get_employees_filtered(p_last_name in varchar2, p_salary in number)
      return sys_refcursor
      is
        v_employees sys_refcursor;
      begin
        open v_employees for select * from employees where last_name like ‘%’ || p_last_name || ‘%’ and salary < p_salary;
        return v_employees;
      end get_employees_filtered;

Walkthrough

  1. Open Visual Studio 2010
  2. Add a New Project
  3. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
  4. Give your project a name i.e BCSMetaManFilteredOracleStoredProcedure and click “OK”

    Create BCSMetaManFilteredOracleStoredProcedure project

  5. On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

    Deploy as a farm solution

  6. If prompted, click “Trial” on the licensing dialog
  7. 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

    Open the BCS Meta Man Data Source Explorer

  8. Click the “Add Connection” button to show the “Connection Dialog”
  9. Select “ODBC Server” as Data Source type, enter Oracle ODBC connection string (for example it can be “Driver={Oracle in OraClient11g_home1};Dbq=XE_AURIGA;Uid=hr;Pwd=hr;Trusted_Connection=yes;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.

    Oracle ODBC connection string

  10. The Data Source Explorer will now be populated with your Oracle data

    See the stored procedures available to use 

  11. Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”

    Drag and drop a Finder stored procedure and Enter External Content type name

  12. Select the Method Type as “Finder’’
  13. Select the first Parameter (p_last_name) and set it’s Filter Type to Comparison and the Default Value to ‘a’

    Configuring Finder Oracle stored procedure and first parameter

  14. Next select the second Parameter (p_salary) and also set it’s Filter Type to Comparison and the Default Value to ‘4000’

    Configuring Finder Oracle stored procedure and second parameter

  15. Click