Sample BDC Model: Connecting to an Oracle Database
Applies to: SharePoint Server 2010
The following example shows how to use a set of LobSystem and LobSystemInstance properties to configure connection to an Oracle database. For more information, see How to: Connect to an Oracle Database Using Business Connectivity Services.
Prerequisites
Oracle Client must be installed in the server farm and the TNS Net Service must be configured to connect to Oracle from Microsoft SharePoint 2010. The following steps show how to configure Oracle Client for a 1m farm. These steps are necessary to use Profile store import and external lists.
Install Oracle Client. Only the base client installation is needed.
Launch Oracle Client and click Configuration and Migration Tools. Then, click Net Manager.
Choose a name for the Net Service.
Set the connection protocol (TCP/IP).
Provide a hostname and port number for the database.
Provide the Oracle service name if using Oracle Client 8i. For earlier versions of Oracle Client, provide the SID.
For a multi-computer farm, to enable Profile store import, install the Oracle Client on the application server. For Profile pages, external lists, and Business Data Web Parts, Oracle Client must be installed on each front-end Web server that will host the external list.
Example
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model xmlns="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog
BDCMetadata.xsd" Name="OracleHRDB">
<LobSystems>
<LobSystem Type="Database" Name="OracleHR" DefaultDisplayName="Oracle 2">
<Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
<LobSystemInstances>
<LobSystemInstance Name="Oracle HR Instance">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">RdbCredentials
</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">Oracle
</Property>
<Property Name="RdbConnection Data Source" Type="System.String">
YOUR_ORACLE_NET_SERVICE_NAME_HERE</Property>
<Property Name="SsoApplicationId" Type="System.String">
SECURESTORE_ORACLE_APP_ID_HERE</Property>
<!-- Server ship
<Property Name="SsoProviderImplementation"
Type="System.String">
Microsoft.Office.SecureStoreService.Server.SecureStoreProvider,
Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c</Property> -->
<!-- Client Ship -->
<Property Name="SsoProviderImplementation"
Type="System.String">
Microsoft.Office.BusinessData.Infrastructure.SecureStore.LocalSecureStoreProvider,
Microsoft.Office.BusinessData, Version=14.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity EstimatedInstanceCount="10000" Name="Employee"
DefaultDisplayName="Employee" Namespace="HR.OracleModel" Version="1.0.0.0">
<Properties>
<Property Name="Title" Type="System.String">EName</Property>
</Properties>
<Identifiers>
<Identifier TypeName="System.String" Name="EmployeeName" />
</Identifiers>
<Methods>
<Method Name="EmployeeFinder">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
FROM SCOTT.EMP
WHERE ENAME LIKE :Name
ORDER BY EMPNO</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="Wildcard" Name="EmployeeName" />
</FilterDescriptors>
<Parameters>
<Parameter Direction="In" Name=":Name">
<TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName"
AssociatedFilter="EmployeeName" Name="EmployeeName" >
<DefaultValues>
<DefaultValue MethodInstanceName="IdEnumeratorInstance"
Type="System.String">%</DefaultValue>
<DefaultValue MethodInstanceName="EmployeeFinderInstance"
Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction="Return" Name="Employees">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
IsCollection="true" Name="Employees">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
Name="Employee">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Decimal" Name="EMPNO" />
<TypeDescriptor TypeName="System.String" ReadOnly ="true"
IdentifierName="EmployeeName" Name="ENAME" />
<TypeDescriptor TypeName="System.String" Name="JOB" />
<TypeDescriptor TypeName="System.Decimal" Name="MGR" />
<TypeDescriptor TypeName="System.DateTime" Name="HIREDATE" />
<TypeDescriptor TypeName="System.Decimal" Name="SAL" />
<TypeDescriptor TypeName="System.Decimal" Name="COMM" />
<TypeDescriptor TypeName="System.Decimal" Name="DEPTNO" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Finder" ReturnParameterName="Employees"
ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0"
Name="EmployeeFinderInstance" >
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
<MethodInstance Type="SpecificFinder" ReturnParameterName="Employees"
ReturnTypeDescriptorName="Employee" ReturnTypeDescriptorLevel="1"
Name="EmployeeSpecificFinderInstance" >
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
<MethodInstance Type="IdEnumerator" ReturnParameterName="Employees"
ReturnTypeDescriptorName="Employees" ReturnTypeDescriptorLevel="0"
Name="IdEnumeratorInstance" >
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Execute"/>
<Right BdcRight="Edit"/>
<Right BdcRight="SetPermissions"/>
<Right BdcRight="SelectableInClients"/>
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="Update" DefaultDisplayName="EmployeeUpdater">
<Properties>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
Text</Property>
<Property Name="RdbCommandText" Type="System.String">
UPDATE SCOTT.EMP
SET EMPNO=:EmpNo,JOB=:Job,MGR=:Mgr,HIREDATE=:HireDate,
SAL=:Sal,COMM=:Comm,DEPTNO=:DeptNo
WHERE ENAME=:Name</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SetPermissions" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Direction="In" Name=":EmpNo">
<TypeDescriptor TypeName="System.Decimal" UpdaterField="true"
Name="EMPNO" />
</Parameter>
<Parameter Direction="In" Name=":Name">
<TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName"
Name="EmployeeName" >
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name=":Job">
<TypeDescriptor TypeName="System.String" UpdaterField="true"
Name="JOB" />
</Parameter>
<Parameter Direction="In" Name=":Mgr">
<TypeDescriptor TypeName="System.Decimal" UpdaterField="true"
Name="MGR" />
</Parameter>
<Parameter Direction="In" Name=":HireDate">
<TypeDescriptor TypeName="System.Nullable`1[[System.DateTime, mscorlib,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
UpdaterField="true" Name="HIREDATE" />
</Parameter>
<Parameter Direction="In" Name=":Sal">
<TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
UpdaterField="true" Name="SAL" />
</Parameter>
<Parameter Direction="In" Name=":Comm">
<TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
UpdaterField="true" Name="COMM">
<Properties>
<Property Name="Decimal Digits" Type="System.Int32">9</Property>
</Properties>
</TypeDescriptor>
</Parameter>
<Parameter Direction="In" Name=":Deptno">
<TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
UpdaterField="true" Name="DEPTNO">
<Properties>
<Property Name="Decimal Digits" Type="System.Int32">9</Property>
</Properties>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Updater" Name="Update"
DefaultDisplayName="SQLAllTypes Update">
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SetPermissions" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
<Method Name="Delete" DefaultDisplayName="EmployeeDelete">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
DELETE FROM SCOTT.EMP WHERE ENAME = :Name</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data,
Version=1.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
Text</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SetPermissions" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
<Parameters>
<Parameter Direction="In" Name=":Name">
<TypeDescriptor TypeName="System.String" IdentifierName="EmployeeName"
Name="EmployeeName" >
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Deleter" Name="Delete" DefaultDisplayName="Employee
Delete">
<AccessControlList>
<AccessControlEntry Principal="redmond\domain users">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SetPermissions" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
</MethodInstance>
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
</LobSystems>
</Model>