Oracle and the Business Data Catalog work really well together – just as SQL Server and the BDC do. If you want to use tables and views from Oracle then everything will work simply. Unfortunately Oracle stored procedures do not work with MOSS 2007 Business Data Catalog – nothing we can do about this – so we have to use Tables or Views.
The Application Definifion file the Oracle and the Business Data Catalog is pretty much the same as SQL server with a few small changes
– we use an Oracle account to connect
– parameters use a : symbol to prefix the names rather than @
– column names are delimited with ” ” rather than []
If you want to test Oracle functionality there is a express version available to download from here:
http://www.oracle.com/technology/products/database/xe/index.html
Read the getting started documentation with this download as it will walk you through how to setup a sample HR database which is what we’ll use here with the Business Data Catalog. So once you have this database up and running lets open BDC Meta Man and get going:
1, Open up BDC Meta Man and on the connection form select Oracle, enter your database location and oracle username and password
2, Expand the database to see all the tables that are available to you. We want to create BDC entities for departments and employees. Drag these two tables onto your design surface.
3, There exists a one to many relationship between our department and employee entities so we can drag and drop from teh department_id in department -> department_id in employees
4, On the main menu choose Configuration -> Settings and choose a place to generate your BDC application definition file to.
5, No back on the main screen, press the play button and your Business Data Catalog application definition file will be generated. You’ll get prompted to open it also, which if you do you’ll see all the XML that BDC Meta Man has generated for you.
6, Now we want to import our ADF file. Open Central Administration, and then click the link for your Shared Service Administration page.
7, Click the link to Import application definition. Browse to your file and click the Import button.
The Business Data Catalog will now go through and check all your XML is valid. It doesn’t actually check that it can connect to Oracle at this point.
8, Browse to your SharePoint site. We are now going to add the BDC web parts to display your Oracle information. Go Site Actions -> Edit Page
9, Click Add a Web Part, when the web part gallery opens up select the Business Data List and the Business Data Related List web parts
10, Click the link to Open the tool pane on the Business Data List Web Part. When the toolbar properties side window opens up select Departments as the Type you want to display, then click OK.
11, Click the link to Open the tool pane on the Business Data Related List Web Part. In the toolbar properties select the Type to be Employees, then click OK.
12, Now we need to connect our two web parts together so that the Department web parts sends the departmentid selected value to the Employees web part. In the Department web part click the Edit link then Connections -> Send Selected Item to -> HR.Employees List.
13, Once the page has refreshed you’ll be able to select a department (notice the radio button now next to each row of data), and the relevant employees will be returned in the related list.
We hope this walkthrough was useful to you. If you have any questions feel free to email them to support@lightningtools.com.
Remember you can also watch this walkthrough as a web cast.
