How to connect SharePoint BDC to Oracle through TNS

By admin Uncategorized 8th July 2012

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.

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.oraIn 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
- 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
- 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
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
Now we should click the drop down menu “Connect to data source” and 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.
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 startedarticle in details. We will walkthrough briefly.
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.
Open menu item Configuration->Settings in order to set the path to save our Oracle application definition file.
Click “Generate” button on BDC Meta Man toolbar. If everything is ok then we will see this message.
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 startedarticle describes in details how to do that). Now we will be able to see Oracle data on SharePoint page.