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 an existing database table.
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 use an Oracle table containing information on Vancouver's bike path. Additional data will be loaded from an Esri Shapefile format and written to the existing Oracle table.
1. Add an Esri Shapefile Reader
Start FME Workbench, and select the New workspace option.
First, we need to add a source dataset. Click on the Reader button. In the Add Reader dialog, enter the following:
- Format: Esri Shapefile
- Dataset: Bike_Paths_L.shp
Click OK to add the bike path data to the workspace.
2. Add an Oracle Spatial Object Writer
Now the data needs to go to the destination. Click on the Add Writer button on the toolbar. In the Add Writer dialog, enter the following:
- Format: Oracle Spatial Object
-
Connection: <your database connection>
- For information on how to connect to your own Oracle database instance, please see Viewing and Inspecting Oracle Data
- Table Definition: 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'.
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 has been 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 fields are set:
- Feature Operation: Insert
- Table Handling: 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, new data will be added repeatedly!