External Oracle data into BDC List web part

By peter.stilgoe









Install Oracle instant Client on Sharepoint server

Download Oracle instant client from

http://www.oracle.com/technology/tech/oci/instantclient/index.html

Unzip the file into C:\Program Files\Oracle\instantclient_11_1

In the PATH environment variable, add this directory to the end.

Create a new system variable TNS_ADMIN and set to C:\Program
Files\Oracle\instantclient_11_1

Ensure your tnsnames.ora file is in this directory with the database connection defined

Log out and then back into machine to take effect.

Install BDC Metaman on Sharepoint server

Download BDC Metaman from http://www.lightningtools.com

Start BDC Metaman

Select Configurations->Settings

In the “Application Definition Filename” textbox, enter the .xml file you will be saving to (i.e. mydbconnect.xml)

In the “Namespace” textbox, enter a name i.e. “MYSHAREPOINT”

Click on “Connect to Datasource” dropdown->Oracle

In the “Connect to Database Source” dialogue box:
“Server Type” should be “Oracle”
“Server Name” should be the Database instance in your tnsnames.or a file

Enter Username and Password to connect to the database and click on “Connect”. After a few moments, a collapsible menu should appear in the left-hand pane.

Expand the list of tables and drag the table you require to the right-hand pane.

Click on the green arrow to generate your Application Definition xml file into the filename you specified earlier. We will use this as a template to import into Sharepoint in later steps.

Configure Sharepoint Single Sign-On

Follow the steps exactly from Robert Bogue’s excellent site under “Create the SSO Service Account” and “Create the SSO Groups” in the following document:

http://thorprojects.com/blog/archive/2008/08/02/moss-single-sign-on-setup-step-by-step.aspx

In the web document, under “Configure the SSO Service”, after step (3), Under “Logon”, select the radio button next to “This account” and enter the Single SignOn account details as outlined in the web document (e.g. DEMO\SharePointSSOSvc).

Start the service as per the web document.

The next part of the web document shows how to add database privileges, under the heading “Configure SQL Server for the SSO Service Account”. If you don’t have the SQL Server management studio installed on the server, you can issue the following commands:
Cmd osql -E -S important\officeservers
exec sp_grantlogin ‘DEMO\SharePointSSOSvc’
exec sp_addsrvrolemember ‘DEMO\SharePointSSOSvc’,'dbcreator’
exec sp_addsrvrolemember ‘DEMO\SharePointSSOSvc’,'securityadmin’
go

Follow the steps outlined at the following address:

http://thorprojects.com/blog/archive/2008/08/02/moss-single-sign-on-setup-step-by-step.aspx

At the “Create Enterprise Application Definition” screen, make a note of the text you enter in the “Application Name” box (e.g. “my_sso”). You will soon use this text string as you will need to modify your xml file before you create your BDC List web part.

Select “Individual” as Account Type

Leave “Username” in field1

Leave “Password” in field2

Click on “OK”

Go back into “Manage Single Sign-On Settings”-> Manage account information for enterprise application definitions

In the “Enterprise application definition” dropdown select the Application you created earlier.

In “User account name”, enter “DEMO\SharePointSSOSvc”

Ensure “Update account information” is selected

Click “Set”

In the next screen where you’re asked to provide the Connection Account Information, enter the username and password for the Oracle Database you’re connecting to.

Click on “OK”. You will be returned to the previous screen.

Click on “Done”.

Modify and import the BDC xml file

Find the xml file you created with BDC Metaman

Find the following lines:

When connecting to an external Oracle database, the connection credentials are ignored and the single sign-on settings are used.Replace with the following:

Save the file

Go back to the Sharepoint admin homepage.

If already logged in as “DEMO\SharePointSSOSvc”, go to top right of page, go to the drop-down where the user logged in is shown (next to Welcome) and “Sign In as Different User” and log in as the Administrator

Go to “SharedServices1”->Import application definition under “Business Data Catalog”

In the textbox for “Application Definition file”, browse to the xml file you saved previously.

Click on Import. This process will import and validate the xml file and create the application definition.

Create a BDC List Web Part

Go to your page where you want to add the web part.

Click on Site Actions->Edit Page on top right of page

Click on “Add a web part” where you want to add the BDC List

Select “Business Data List” in the list of web parts to add

Click on the text “open the tool pane” which appears in the new web part

In the new pane that opens on the right-hand side, click on the small book icon to the right of the text box (may have to scroll the entire page right)

Select your Oracle application you created previously

Click on “OK”.

Now your Oracle data should appear in your webpart.

  • Share/Bookmark

More From pstilgoe

pstilgoe uses Arkayne Socialize to increase relevant traffic.

,

About... peter.stilgoe

This author published 368 posts in this site.
Sharepoint, InfoPath, K2, Nintex, Business Process Mapping, Business Intelligence, Automation, ECM, Document Management, Document Imaging, Internet Marketing & Online Business Consultant Email / MSN: pstilgoe@hotmail.com LinkedIn: Pete Stilgoe - Sharepoint Consultant











Share

FacebookTwitterEmailWindows LiveTechnoratiDeliciousDiggStumbleponMyspaceLikedin

You must be logged in to send a comment.