connect to Oracle for dashboards

Question

I'm in the process of creating dashboards that were creating by multiple Access databases which used mulitple queries and Oracle Views. Right now I created the Oracle database in my SQL evnironment so I can start creating the dashboards.

Is there a way to connect SharePoint to Oracle via a linked server or something so I can use the existing views to get my data and not use SQL server at all?

Answer 1

Yes, you can connect to Oracle database using BCS. There is no out of the box to do it. We did using bcs connection and pulled the users information for the UPS service.

---

B Singh

Answer 2

Hi,

Unfortunately SharePoint Designer doesn't support connecting to Oracle database. So you may follow one of the following approaches:

1. Manually create a BDC Model for Connecting to Oracle Databases from the beginning How to: Connect to an Oracle Database Using Business Connectivity Services
2. Create a Web service to provide an interface to the external data exposed in the database
3. Use Visual Studio's BDC Model project and write the code for Oracle connection. Please look at this blog post Business Data Connectivity Model – Finder Method

You can also try out the product provided by the company where I work for, and name of that product is BCS Meta Man . It will allow you to generate both Database model and C# code automatically as soon as you drag and drop database tables to the design surface.

It supports Oracle and ODBC connection.

Please look at these blog posts and videos:

• Business Connectivity Services and Oracle with BCS Meta Man
• Oracle and the Business Connectivity Services
• SharePoint 2010 Secure Store Service and Oracle
• Oracle Stored Procedures and the Business Connectivity Services in SharePoint 2010
• Business Connectivity Service Associations using Oracle Stored Procedures in SharePoint 2010
• BCS filters and Oracle Stored Procedures with Parameters in SharePoint 2010

Please take a look at this sample BDC Model for Oracle database:

<?xml version="1.0" encoding="utf-8"?>
<Model Name="BCSMetaMan1Model" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <LobSystems>
    <LobSystem Name="BCSMetaMan1" Type="Database">
      <LobSystemInstances>
        <LobSystemInstance Name="BCSMetaMan1Instance">
          <Properties>
            <Property Name="ShowInSearchUI" Type="System.String"></Property>
            <Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <Property Name="RdbConnection Data Source" Type="System.String">auriga</Property>
            <Property Name="RdbConnection Integrated Security" Type="System.String"></Property>
            <Property Name="RdbConnection User ID" Type="System.String">hr</Property>
            <Property Name="RdbConnection Password" Type="System.String">hr</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Name="REGIONS" Namespace="BCSMetaMan1" Version="1.0.0.0">
          <Properties>
            <Property Name="OriginalTableName" Type="System.String">"HR"."REGIONS"</Property>
            <Property Name="IsCustomCode" Type="System.Boolean">false</Property>
            <Property Name="Title" Type="System.String">REGION_NAME</Property>
          </Properties>
          <Identifiers>
            <Identifier Name="REGION_ID" TypeName="System.Decimal" />
          </Identifiers>
          <Methods>
            <Method Name="GetAllREGIONSEntitys">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS"</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
                          <TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetAllREGIONSEntitys" Default="true" Type="Finder" ReturnParameterName="returnparameter" />
              </MethodInstances>
            </Method>
            <Method Name="GetSingleREGIONSEntityByID">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID", "REGION_NAME" from "HR"."REGIONS" where "REGION_ID"=:REGION_ID</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name=":REGION_ID" Direction="In">
                  <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
                </Parameter>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSList" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONS" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" ReadOnly="true" />
                          <TypeDescriptor Name="REGION_NAME" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetSingleREGIONSEntityByID" Default="true" Type="SpecificFinder" ReturnParameterName="returnparameter" ReturnTypeDescriptorPath="REGIONSList[0]" ReturnTypeDescriptorLevel="1" />
              </MethodInstances>
            </Method>
            <Method Name="GetAllREGIONSEntityIDs">
              <Properties>
                <Property Name="BackEndObject" Type="System.String">REGIONS</Property>
                <Property Name="BackEndObjectType" Type="System.String">OracleTable</Property>
                <Property Name="RdbCommandText" Type="System.String">select "REGION_ID" from "HR"."REGIONS"</Property>
                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                <Property Name="Schema" Type="System.String">HR</Property>
              </Properties>
              <Parameters>
                <Parameter Name="returnparameter" Direction="Return">
                  <TypeDescriptor Name="REGIONSIDs" TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <TypeDescriptor Name="REGIONSSimpleElement" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <TypeDescriptor Name="REGION_ID" TypeName="System.Decimal" IdentifierName="REGION_ID" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <MethodInstance Name="GetAllREGIONSEntityIDs" Default="true" Type="IdEnumerator" ReturnParameterName="returnparameter" />
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>

Hope this makes sense.

---

Dmitry

Lightning Tools Check out our SharePoint tools and web parts | Lightning Tools Blog

Answer 3

In addition to great suggestion from Dmitry, I would like to introduce you to Microsoft BizTalk Adapter for Oracle database. This tool basically helps you access stored procedures, tables and views for Oracle Database that run on Oracle 8i or 9i databases (I don't know if Oracle 10, 11 or 12 could be supported). The following references you should learn:

• Biztalk Server integration with SharePoint Server using WSS adapter
• Biztalk + SharePoint: 1+1=3: Integration Best Practices

---

Thuan Soldier
SharePoint Vietnam | Blog | Twitter