How to: Connect to an Oracle Database Using Business Connectivity Services

  • Article

Applies to: SharePoint Server 2010

The Microsoft Business Connectivity Services (BCS) can connect to external data from the following types of databases:

  • Microsoft SQL Server

  • Oracle

  • OLE DB

  • ODBC

The How to: Create an External Content Type Based on a SQL Server Table topic explains the basic way to work with Business Connectivity Services to show external data from a SQL Server database. When you work with other databases such as Oracle, OLE DB, and ODBC, you must follow one of the following approaches, as these databases are not supported by Microsoft SharePoint Designer 2010:

  1. Create a Business Connectivity Services model from the beginning. For information, see Authoring BDC Models. See the following section for specific things you should consider when writing or modifying models for Oracle databases.

  2. Create a Web service or a .NET connectivity assembly to provide an interface to the external data exposed in the database. For more information, see Creating Web and WCF Services for Business Connectivity Services and How to: Create a .NET Connectivity Assembly.

Creating a BDC Model for Connecting to Oracle Databases

Follow the procedure in Authoring BDC Models to create a model from the beginning.

When writing or modifying BDC models for connecting to Oracle, you should remember the following things:

  • The Oracle SQL syntax requires you to specify parameters in your query by prefixing them with a colon (:) instead of at sign (@). Be sure to set these correctly in the SQL statements in your metadata.

  • If your Oracle connection requires explicit user ID and password parameters in the connection string:

    1. Set up an application definition in Secure Store Service with the Oracle credentials.

    2. Use AuthenticationMode of RdbCredentials.

    3. When you use RdbCredentials as the authentication mode, you cannot use the RdbConnection User ID and RdbConnection Password properties, as these values are supplied by the Secure Store Service. If you specify them, they are simply ignored. You must use Secure Store to supply the Oracle credentials.

  • The Oracle client must be installed on all the computers in the farm and TNS net service must be configured to connect to Oracle from SharePoint. This is required for external lists, Web Parts, and profile pages on the server. As applications such as search run only in App Server, it is okay to install the Oracle client on the application server. Search will connect only from the application server. So in short, the Oracle client must be installed on the computers on the farm from which you will connect to Oracle. The Oracle client also must be installed on the rich client machines for the same reason.

The following example shows you how to set the LobSystemInstance properties for an Oracle database that is connecting using Secure Store Service. Replace YOUR_ORACLE_NET_SERVICE_NAME_HERE with the TNS net service name and SECURESTORE_ORACLE_APP_ID_HERE with the name of the enterprise application definition you set up in the Secure Store Service.

<?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>