Converting to Oracle: Create, Drop or Truncate a Table

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, DELETE, and fme_db_operation.

• INSERT: records will be added (appended) to the database. This can be part of an update where the entire contents of a table are deleted and replaced with new features.
• UPDATE: records will not be inserted or deleted, but replaced (depending on the row selection method used). Each FME feature written to a database in UPDATE mode replaces an existing database record.
• 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 the attribute defined in the fme_db_operation for each input feature. 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: drop the destination 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 in as points, and writing a new table in Oracle. The data for use has been provided in the downloads section.

1. Add a CSV Reader

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 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. Click OK to dismiss the CSV parameters dialog, but do not exit out of the Add Reader dialog.

Set the coordinate system to LL84. Each point will be tagged with this coordinate system as it's read. Click OK to dismiss the Add Reader dialog.

2. Add the Oracle Spatial 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 Oracle.

3. Set the Table Name and Table Qualifier parameters

After adding the Writer, access the feature type 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. On the connection, right-click and select 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’.

Replace a Table Using a Table Handling Parameter

Using a table handling parameter, it is possible to replace the entire 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 of no use when the existing table has been emptied first.