How to connect SharePoint BDC to Oracle through TNS

How to connect SharePoint BDC to Oracle through TNS
0
By admin Uncategorised 4th March 2010

In this walkthrough we will demonstrate how to integrate Oracle with Microsoft SharePoint through the Oracle Transparent Network Substrate (TNS) and display Oracle data on SharePoint page. In order to do that we will describe how to create application definition files for the Business Data Catalog by means of BDC Meta Man.

There are one articles which we are going to reference to. It describes Business Data Catalog – getting started

This walk through only applies to BDC Meta Man 4.0.0.7 and later.

  1. Prerequisites.
  • First of all we should have tnsnames.ora file. It can be taken from a machine where Oracle runs or you may create it manually. If you look at this post you may find detailed information regarding tnsnames.ora http://www.orafaq.com/wiki/Tnsnames.ora In our case a file will look as follows (for example) and we will put it to “D:/Work” folder:

    XE_ORION =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = Orion)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = XE)
        )
      )

    ORCL_CYGNUS =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = Cygnus)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = ORCL)
        )
      )

  • As BDC MetaMan uses Oracle Data Provider for .NET (ODP.NET) in order to connect to Oracle database you should install Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (32-bit). 
    It can be found here
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html 
    Please ensure you select the ‘Administrator’ type of installation when you install Oracle clients

    Select the Administrator type of installation

  • If SharePoint and BDC Meta Man are running on different machines and you have SharePoint 2007 running on an x86 operating system  you should also have Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows  (32-bit) installed on the machine where SharePoint runs. But if you are using SharePoint on a 64 bit operating system you should install Oracle Database 11g Release 2 Client (11.2.0.1.0) for Microsoft Windows (x64). 
    It can be found here
    http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win64soft-094461.html 
    Please ensure you select the ‘Administrator’ type of installation when you install Oracle clients

    Select the Administrator type of installation

  • Run “iisreset” command line utility after installation of Oracle client on a machine where SharePoint 2007 is running.

  • If you are using BDC Meta Man on a 64 bit operating system when connecting to Oracle you may be prompted with the error: “ORA-06413: Connection not open”. This seems to be a problem with Oracle and any application installed in a directory that has brackets “()” in it (you may find a description here http://itknowledgeexchange.techtarget.com/itanswers/solution-for-ora-6413-error-showinh-connection-not-open/). If you check BDC Meta Man may be installed at: “C:Program Files (x86)BDC Meta Man”. The solution to get around this is to change the default installation path to something like: “C:BDC Meta Man”
  • Now open BDC Meta Man and select menu Configuration->Settings

    Menu Configuration->Settings

  • In the “Configuration Options” dialog select “Oracle” item in the left list box, click “Browse” button, point tnsnames.ora file location and click “Save” button

    Point tnsnames.ora file

  • Now we should click the drop down menu “Connect to data source” and select “Oracle” menu item.

    Select Oracle menu item

  • After that we are able to select Oracle database from a drop-down list on “Connect to Data source” dialog and enter user name and password.

    Select Oracle database and type username and password

  • After clicking “Connect” button BDC Meta Man will now connect to your Oracle data source and display the database. Expand the database you have connected to so you can see all the tables available for you to use. All the next steps about how to display the data on SharePoint page are described in Business Data Catalog – getting started article in details. We will walkthrough briefly.

    All Oracle tables are available to use

  • Drag and Drop one table onto Design Surface. Upon dragging and dropping the table, the entity is created with the Finder, SpecificFinder and IdEnumerator methods.

    Drag and Drop one table onto Design Surface

  • Open menu item Configuration->Settings in order to set the path to save our Oracle application definition file.

    Set the path to save application definition file

  • Click “Generate” button on BDC Meta Man toolbar. If everything is ok then we will see this message.

    Oracle application definition file has been created

  • Import generated application definition file to SharePoint 2007, add Business Data List web part and configure it by selecting just imported Business Data catalog Entity (Business Data Catalog – getting started article describes in details how to do that). Now we will be able to see Oracle data on SharePoint page. 

    Oracle data on SharePoint page

    • We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com.

    <dmitry/>

    admin / About Author
    More posts by admin

    Leave a comment