Files
Introduction
FME has two feature type writer parameters to allow users to control how data is written into a database; Feature Operation, and Table Handling.
Feature Operation
Updates and deletions to a database can be primarily controlled through a Writer parameter called Feature Operation. This parameter informs FME what action to carry out on the database. The values that can be used for the feature operation are INSERT, UPDATE, UPSERT, DELETE, and fme_db_operation.
- INSERT: Records will be added (appended) to the database. This can be part of an update in which the entire contents of a table are deleted and replaced with new features.
- UPDATE: Records will neither be inserted nor deleted; instead, they will be replaced based on the “Row Selection” method. Each FME feature written to the database in UPDATE mode replaces an existing record.
- UPSERT: Records will be inserted if new or updated for existing rows, based on the selected "Row Selection" method, using input feature attributes and/or geometries.
- DELETE: Records will be removed (deleted) from a database (depending on the row selection method used). Each FME feature written to a database in DELETE mode causes a database record to be deleted.
- fme_db_operation: This feature operation action is determined based on each input feature's attribute defined in the fme_db_operation. Refer to the feature operations documentation for further details.
Table Handling
Table Handling parameters exist to help create or update existing database tables. Table Handling makes the following options available:
- Use Existing: Use the existing table
- Create If Needed: If no table currently exists, one will be created
- Drop and Create: If the table exists, drop the table and create it
- Truncate Existing: Delete all rows from the existing table
Video
Step-by-step Instructions
In this tutorial, we will load data to create a new table, then demonstrate how to replace a table using a Table Handling parameter.
Create a New Table
This demonstration will create a new table by loading Downtown Vancouver public art data, reading the information as points, and writing a new table in Oracle. The data for use has been provided in the files section on the right side of the page.
1. Add a CSV Reader
As of FME 2025.2, the Coordinate System parameter is now configured within the Parameters dialog of each reader/writer format. For more information, including details about the change and affected transformers, please see Coordinate System Parameter Location Change.
Start FME Workbench, and select the New workspace option.
Add a new reader to the canvas. In the Add Reader dialog, select CSV for the source format. Select the 4216-downtown.csv file for the Dataset.
Click on the Parameters button. In the Attributes panel, set the Attribute Definition to Manual. You can now set the columns ‘Longitude’ and ‘Latitude’ as type ‘x_coordinate’ and ‘y_coordinate' respectively. This tells the reader to generate point features from the CSV data as it reads the file. In this example, FME automatically recognizes the longitude and latitude columns, so we can click OK to dismiss the CSV parameters dialog, but do not exit the Add Reader dialog.
Set the coordinate system to LL84. As it's read, each point will be tagged with this coordinate system. Click OK to dismiss the Add Reader dialog.
For FME 2025.2 and newer, enable Create Point Geometry From Attributes in the
Parameters dialog, then set the Coordinate System.
2. Add the Oracle Spatial Writer
Add a new writer and select the Oracle Spatial Object format. Choose an existing Oracle database connection instance, or click “Add Database Connection” to create a new one if needed. Refer to the Viewing and Inspecting Oracle Data article if you need additional details for connecting to Oracle.
3. Set the Table Name and Table Qualifier parameters
After adding the Writer, access the feature type's general parameters. Set the Table Name to ‘DOWNTOWN_PUBLICART’ and the Table Qualifier to a Schema being used in the Oracle database. (For our demonstration workspace, we will set the Schema to ‘SUPPORT’) The Feature Type writer is specified per database table, as one could write to multiple schemas using the same database writer.
4. Set the Feature Operation and Table Handling parameters
Because we are creating a new table in the Oracle database and adding data to it, the Feature Operation and Table Handling parameters will be left at their default setting; Feature Operation: ‘Insert’, and Table Handling: ‘Create If Needed’.
5. Map Attributes
Connect the CSV feature type reader and the Oracle Spatial feature type writer. Select Oracle Spatial writer and click on the ‘Gear’ icon, then select ‘User Attributes’ tab.
The Attribute Definition defaults to ‘Automatic,’ which automatically copies all attributes of the incoming feature type. If the source dataset changes, the existing attributes in the data will remain unchanged. The ‘Manual’ option allows users to customize attribute names, types, and default values. The ‘Dynamic’ option updates attributes to reflect changes in the source dataset.
6. Run the Workspace
Run the workspace, and view the result by right-clicking the writer and choosing ‘inspect’.
Replace a Table Using a Table Handling Parameter
Using a table handling parameter, it is possible to replace all the contents of a table. The parameters to accomplish this are ‘Drop and Create’ and ‘Truncate Existing’.
‘Truncate Existing’ is used when the table needs to be emptied of existing data, but does not otherwise need an update to its schema. ‘Drop and Create’ is used when the table needs to be emptied AND an update is to be made to the database schema. For example, use a ‘Drop and Create’ when you wish to update a table with new content and require a new column to be added to the table.
It is also necessary to set the Feature Operation parameter to INSERT. UPDATE and DELETE will be useless when the existing table has been emptied first.