Whitepapers - Oracle desktop integration

By Ben Lamb, 4th February 2005

This article explains how to merge simple table data from an Oracle database into a Word document using the Mail Merge facility.

It assumes you have a ODBC connection configured to your Oracle database and are running Word 2002 or later. See the article Installing the Oracle ODBC driver for more information.

To merge data using complex queries (parameters, table joins) into Word, see our Advanced Word Mail Merge article.

Display the Mail Merge Toolbar

The most common mail merge functions can be accessed from the Mail Merge toolbar. To add this toolbar, go to

Tools -> Letters and Mailing -> Show Mail Merge Tool Bar

This will display a Mail Merge toolbar to the screen.

Connect to the Oracle data source

To connect to the database you must open a data source. This is done by clicking the 'Open Data Source' button shown below.

Click new source to add a new data source.

Choose Oracle from the list of different kinds of data sources that you can connect to.

Enter your server name and a username and password to access the database.

It will try and connect, then it will let you choose the table you want to connect to.

You will be asked to save the connection file and if you want, add a description.

After clicking Finish, you will be asked to select a data source.

(Notice that the data source you just created is now in the list)

Once it is selected, click open.

Fill in the fields and click on test connection to see if you can connect to the database.

If the connect is successful the following will be displayed.

If the connect works click OK.

(Note: After I clicked OK I had to re-enter the password and click OK again)

Now that you have completed all of the above, the rest of the toolbar will be active.

Now you can add fields and text to make your document.

Creating the Word Document

To insert a formatted greeting in your document, click the "Insert Greeting" button.

Once pressed you will be able to enter a greeting specifying the format that is apropriete for the document you are creating.

If the greeting seems to be picking up the wrong fields for the persons name, you can match the fields by pressing the "Match Fields" button.

Here you can specify what field from the database matches the Required information by using the drop down box to choose the database fields.

Once added the greeting line will appear as <<GreetingLine>> in the Word document.

If you want to add database fields manually, you can do this by clicking the "Insert Merge Fields" button

From here you can pick the field you want to add and simply click insert.

Below is an example document containing a Greeting and numerous database fields mixed in with some normal text.

If you want to see the document with the data inserted you can click on the "View Merged Data" button

Once this has been pressed you can see what the document will look like. To scroll though all the different records, press the buttons shown below.

Once satisfied with the document, you can export it to a Word document, printer or email. For this example we will export to a word document. This is done by clicking the "Merge to New Document" button.

After selecting All and clicking OK, a new document will appear with each record being shown on a separate page.

You have now created your first Word document that uses Oracle data.

Drawbacks

Using this method there are a number of drawbacks.

• Only one table can be selected.
• No formulas can be entered (excluding the inbuilt word formulas).
• The where condition is very basic and doesn't allow much data filtering.

Next

We explain how to use complex queries and parameters to refine the data you want to merge.

Creating a complex query using MS Query

Solid Rock provides Oracle consulting, application support and development services.

See our Oracle Consulting page for more information.