SharePoint Business Data Catalog - Connecting to Oracle Databases

Business Data Catalog, one of the most powerful feature of SharePoint 2007, lets you connect SharePoint to SQL and Oracle Databases.

BDC requires an Application Definition file to store the connection information and required output columns from the database. One can easily find the sample Application Definition File to connect to SQL Server's database AdventureWorks. But there is little information on web about connecting to Oracle Database. Once I was able to successfully write an Application Definition file to connect to an Oracle database, I thought about sharing it , thus here it is:
Consider you have an Oracle Table called my_bdc_test, with following columns: Employee ID number,
Name varchar2,
Title varchar2,
Hiredate date,
ManagerID number
To create an application def file to extract these columns here is the sample .xml file:

<?xml version="1.0" encoding="utf-8" standalone="yes"?> <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database" Version="1.0.0.2" Name="EmpInfo" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog"> <Properties> <Property Name="WildcardCharacter" Type="System.String">%</Property> </Properties> <LobSystemInstances> <LobSystemInstance Name="EmployeeInfoInstance"> <Properties> <Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property> <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property> <Property Name="RdbConnection Data Source" Type="System.String">data source name</Property> <Property Name="RdbConnection User ID" Type="System.String">username</Property> <Property Name="RdbConnection Password" Type="System.String">password</Property> <Property Name="RdbConnection Unicode" Type="System.String">True</Property> <Property Name="RdbConnection Integrated Security" Type="System.String"></Property> </Properties> </LobSystemInstance> </LobSystemInstances> <Entities> <Entity EstimatedInstanceCount="0" Name="HR.Employee1"> <Properties> <Property Name="DefaultAction" Type="System.String">View Profile</Property> </Properties> <Identifiers> <Identifier TypeName="System.Decimal" Name="EmployeeID" /> </Identifiers> <Methods> <Method Name="GetHR.Employee1"> <Properties> <Property Name="RdbCommandText" Type="System.String">Select EmployeeID,Name,Title,ManagerId,HireDate from my_bdc_test Where (EmployeeID>=:GeneratedMinEmployeeID) and (EmployeeID<=:GeneratedMaxEmployeeID)</Property> <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property> </Properties> <FilterDescriptors> <FilterDescriptor Type="Comparison" Name="EmployeeID" /> </FilterDescriptors> <Parameters> <Parameter Direction="In" Name=":GeneratedMinEmployeeID"> <TypeDescriptor TypeName="System.Decimal" IdentifierName="EmployeeID" AssociatedFilter="EmployeeID" Name="EmployeeID"> <DefaultValues> <DefaultValue MethodInstanceName="HR.EmployeeFinder" Type="System.Decimal">0</DefaultValue> <DefaultValue MethodInstanceName="HR.EmployeeSpecificFinder" Type="System.Decimal">0</DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="In" Name=":GeneratedMaxEmployeeID"> <TypeDescriptor TypeName="System.Decimal" IdentifierName="EmployeeID" AssociatedFilter="EmployeeID" Name="EmployeeID"> <DefaultValues> <DefaultValue MethodInstanceName="HR.EmployeeFinder" Type="System.Decimal">999999</DefaultValue> <DefaultValue MethodInstanceName="HR.EmployeeSpecificFinder" Type="System.Decimal">999999</DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="Return" Name="HR.Employee"> <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="HR.EmployeeDataReader"> <TypeDescriptors> <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="HR.EmployeeDataRecord"> <TypeDescriptors> <TypeDescriptor TypeName="System.Decimal" IdentifierName="EmployeeID" Name="EmployeeID" /> <TypeDescriptor TypeName="System.String" Name="Name" /> <TypeDescriptor TypeName="System.String" Name="Title" /> <TypeDescriptor TypeName="System.Decimal" Name="ManagerId" /> <TypeDescriptor TypeName="System.DateTime" Name="HireDate" /> </TypeDescriptors> </TypeDescriptor> </TypeDescriptors> </TypeDescriptor> </Parameter> </Parameters> <MethodInstances> <MethodInstance Type="Finder" ReturnParameterName="HR.Employee" ReturnTypeDescriptorName="HR.EmployeeDataReader" ReturnTypeDescriptorLevel="0" Name="HR.EmployeeFinder" /> <MethodInstance Type="SpecificFinder" ReturnParameterName="HR.Employee" ReturnTypeDescriptorName="HR.EmployeeDataReader" ReturnTypeDescriptorLevel="0" Name="HR.EmployeeSpecificFinder" /> </MethodInstances> </Method> </Methods> <Actions> <Action Position="1" IsOpenedInNewWindow="false" Url="http://mossint:20000/ssp/admin/Content/HumanResources.Employee1.aspx?EmployeeID={0}" ImageUrl="/_layouts/1033/images/viewprof.gif" Name="View Profile"> <ActionParameters> <ActionParameter Index="0" Name="EmployeeID" /> </ActionParameters> </Action> <Action Position="1" IsOpenedInNewWindow="true" Url="http://www.google.com/search?q={0}" ImageUrl="" Name="Search on Google"> <ActionParameters> <ActionParameter Index="0" Name="Title" /> </ActionParameters> </Action> </Actions> </Entity> </Entities> </LobSystem>

I have added 2 Actions in the application def file , (highlighted in GREEN). The first Action, takes the selected Employee ID from the BDC List webpart and opens the detailed user profile in a BDC Item WebPart (I have connected these 2 webparts) .
The second action searches the "Title" returned from the database in Google and opens a new result window. You can easily omit the Actions if you like.

To get this working in your environment, you have to modify following in this file:

1. Enter the datasource name.
2. Enter username and password to connect to the data source.
3. Modify the SELECT statement for your table.
4. Change EmployeeID to any other number field from your table, making sure its unique and not null.
5. In the TypeDescriptors, change the TypeName (data type) and Name (column name).
   
   To start with you may get some issues/errors, to check and resolve the errors, browse to 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\Logs' and check the latest logs.