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”

   

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

   

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

   

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.

   

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

     

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”

    

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’

    

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

    

15. Click