How to: use business connectivity services to connect to Oracle databases

Microsoft Business connectivity services (BCS) can connect to external data from the following types of databases:

• Microsoft SQL Server

• Oracle

• OLE DB

• ODBC

How to: create external content types based on SQL Server tables this topic describes how to use business connectivity services to display external data from SQL Server databases. When using other databases (such as Oracle, ole db, and ODBC), you must follow one of the following methods (because Microsoft SharePoint designer 2010 does not support these databases ):

1. Create a business connectivity services model from scratch. For more information, see create a BDC model. For more information about writing or modifying Oracle database models, see the next section.

2. Create a web service or. NET connectionProgramSet to provide interfaces for external data exposed in the database. For more information, see creating web and WCF services for business connectivity services and how to: create. net connection assembly.

Create a BDC model to connect to the Oracle database

Follow the process in creating the BDC model to create the model from scratch.

Note the following when writing or modifying a BDC model to connect to Oracle:

• Oracle SQL syntax requires you to specify parameters in the query as follows: add a colon (:) For the parameters, instead of the @ symbol prefix. Make sure that they are correctly set in the SQL statement of metadata.

• If the Oracle connection needs to connect the explicit user ID and password parameters in the string, then:

  1. Use Oracle creden to set application Definitions in secure Store service.

  2. Use the authenticationmode of rdbcredentials.

  3. When you use rdbcredentials as the Authentication mode, you cannot use the rdbconnection user ID and rdbconnection password attributes because these values are provided by the secure Store service. If these values are specified, they are ignored. You must use secure storage to provide Oracle creden.

• The Oracle client must be installed on all computers in the server farm, and the TNS net service must be configured to connect from Sharepoint to Oracle. This is required for the external list, Web parts, and configuration file pages on the server. Because applications (such as search) only run on the application server, you can install the Oracle client on the application server. The search will only be connected from the application server. In short, the Oracle client must be installed on the computer from the server farm that is connected to Oracle. For the same reason, you must also install the Oracle client on the rich client computer.

The following example shows how to set the lobsysteminstance attribute for an Oracle database connected 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 defined by the enterprise application set in the secure Store service.

XML

<? XML version = "1.0" encoding = "UTF-8" standalone = "yes"?> <Model xmlns = "http://schemas.microsoft.com/windows/2007/BusinessDataCatalog" xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" xsi: schemalocation = "http://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. stri NG ">%</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 = custom </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 = custom "> 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> </parameter> </parameters> <methodinstances> <methodinstance type = "Finder" returnparametername = "employees" parameters = "employees" parameters = "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" parameters = "employee" parameters = "1" name = "tags"> <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 "parameters =" employees "parameters =" 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>