Converting to Oracle: Write to an Existing Table

Introduction

As we have seen in previous tutorials, FME is often used to create a new database table from incoming data. However, in some cases it is necessary to write to a database table that already exists.

When writing to a table that exists, the workspace should reflect the table schema. This can be accomplished by importing the table schema from the database when adding the feature type writer. When adding a database writer, the ‘import from dataset…’ type writer. When adding a database writer, the ‘import from dataset…’ option allows the user to select a schema from a table that already exists.

Video

Step-by-Step Instructions

Write to an Existing Table

This demonstration will make use of an Oracle table that contains Vancouver bike path information. Additional data will be loaded from an Esri Shapefile format, and the data will be written to the existing Oracle table.

1. Add the Esri Shapefile Reader

Start FME Workbench, and select the New workspace option.

From the Readers menu, select Add Reader. Enter Esri Shapefile for the format, and select the ‘Bike_Paths_L.shp’ file for the Dataset. Click OK to add the bike path data to the workspace.

2. Add the Oracle Spatial Object Writer

Add a new writer, and select the Oracle Spatial Object format. For the dataset, select ‘add database connection’, and Connect to your Oracle database instance. Refer to the Viewing and Inspecting Oracle Data article if you need additional details for connecting to an Oracle database. Before dismissing the Add Writer dialog, make sure that the Add Feature Type(s) Table Definition is set to: ‘Import from Dataset’. Click OK to accept the writer parameters.

3. Import Feature Types

FME will prompt the user to define the Feature Types to be imported through the Import Writer Feature Types dialog. Click ‘Parameters…’, and select the target table that you would like to use for the schema. For our example, the target table is 'BIKE_PATHS'.

Note: Accompanying data for Bike_Paths has been provided in the downloads section in a zipped format. This exercise will assume that the Bike_Paths data is loaded into your Oracle database prior to beginning step 3.

The schema definition for the selected table is now added to the workspace. Notice that the Writer Feature Type is a copy of the existing database table schema (and not the Reader Feature Type, as a standard workspace would create).

4. Set the Feature Operation and Table Handling

Open the Feature Type Properties for the feature writer. Under the Format Parameters tab, verify that the Feature Operation parameter is set to 'Insert', and change the Table Handling to 'Use Existing'.

5. Map the Schema

Connect the Reader to the Writer, and expand the attributes available for each. You’ll notice that attributes don’t map, and this is because Oracle requires attributes to be in uppercase format. To correct this, right-click the connection and use the Auto Connect Attributes. Auto Connect Attributes is used to replace attribute names defined as lowercase to uppercase as defined in the Oracle output.

6. Run the Workspace

Run the workspace, and view the result by right-clicking the writer and choosing ‘inspect’. Note that if the workspace is run more than once then new data will be added repeatedly!