Writer Feature Type Properties: Format Parameters

This parameter lets the user specify how features will be written into the destination table. Supported feature operations are:

• Insert: Append rows onto the destination table using attributes on features.
• Update: Update existing table columns using attributes on features. A selection method must be specified in the Row Selection group.
• Delete: Delete existing table rows. A selection method must be specified in the Row Selection group.
• fme_db_operation: The feature operation will be determined by the attribute fme_db_operation on each input feature. A selection method must be specified in the Row Selection group. The value of fme_db_operation will be processed as follows:
  • If the value is null, empty, or missing, it will be treated as Insert.
  • The value will next be matched to Insert, Update, and Delete, case insensitively.
  • If there is no match, the feature will be rejected.
  • If there is a match, the matched feature operation will be performed on the feature.

Tip: The fme_db_operation attribute will cause feature rejection when Feature Operation is set to Insert, Update, or Delete. This behavior differs from previous versions of FME.

More information on Feature Operations.

Controls how the feature type handles destination tables or lists. These options are available:

• Use Existing: If destination table/list does not exist, the translation will fail.
• Create If Needed: Create destination table/list if it does not exist.
• Drop and Create: Drop destination table/list if it exists, and then create it.
• Truncate Existing: If destination table/list does not exist, the translation will fail. Otherwise, delete all rows from existing table or list. This option is not available for all formats.

When updating features, users have a choice to update, or skip, their spatial column(s). Possible options are:

• Yes: The spatial column(s) specified by the user will be updated. IFMENulls will be written as null values and replace existing spatial values.
• No: No spatial columns will be updated.

The columns specified in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.

The corresponding WHERE Clause Builder lets you construct a WHERE clause. You can also type in a WHERE clause inline, without launching the WHERE Clause Builder. It is optional to start the clause with the keyword WHERE.

The WHERE Clause Builder makes it easy for users to reference feature attribute values, destination table columns, and invoke FME functions. The WHERE clause is first evaluated as an FME expression, before being passed onto the destination database.

If the WHERE clause is incorrect or if its evaluation results in failure, translation will fail. Otherwise, if the WHERE clause passes FME evaluation but it is SQL invalid, the feature will be rejected or the translation will fail.

For advanced users, conditional FME expressions created through the Conditional Value editor can be used to create WHERE clauses.

Tip: Tip: You can set the WHERE Clause to an attribute. This supports workspace migration and existing workflows involving fme_where. (Direct support for fme_where has been deprecated.) To advanced users who are accustomed to using fme_where, if Feature Operation is set to Update, Delete, or fme_db_operation, an fme_where attribute that conflicts with Match Columns or WHERE Clause will result in feature rejection.

Specifies additional parameters (for example, table allocation characteristics) to be appended to the Oracle CREATE TABLE query used to create the output table. For example, to specify a tablespace, a STORAGE clause, and a comment for a table, the following clause could be appended:

TABLESPACE myTableSpace

STORAGE (INITIAL 50K);

COMMENT ON TABLE myTable IS ‘My new table’;

Specifies the dimension of the layer, which can currently be 2 or 3.

Directs the writer to write measures to the destination table. When this parameter is set to yes and the incoming feature does not have any measures, then null values are written. This parameter applies when writing to new tables.

The default is No.

Minimum X/Y/Z Coordinate and Measure Value

The minimum x, y, z, and measures values expected in the dataset. If any x/y/z/measures values are present that are less than this value, the data will still be written. However, querying the spatial index outside its extents will produce undefined results. For best spatial search performance, this value should be as close to the true minimum x/y/z/measures value as possible.

Maximum X/Y/Z Coordinate and Measure Value

The maximum x, y, z, and measures values expected in the dataset. If any x/y/z/measures values are present which are greater than this value, the data will still be written. However, querying the spatial index will produce undefined results. For best spatial search performance, this value should be as close to the true maximum x/y/z/measures value as possible.

Comparison Tolerance for X/Y/Z Coordinate and Measures

This specifies the comparison tolerance for the x, y, z, and measures values. Values in x/y/z/measures that are closer than this value are considered equal.

When writing to the object model, the Oracle Spatial Object writer will create a column to hold the geometry. This column is typically called GEOM, but may be changed to any other name by using this parameter.

Note that this parameter merely specifies a default value for the geometry column. If the table already exists in the Oracle database, then the geometry column will be chosen from those defined on the existing table.

Specifies the spatial referencing information for the geometry in the table. It is specified as an integer, and corresponds to the spatial reference identifier (SRID) column in the global table MDSYS.CS_SRS.

All geometry within a given table must have the same spatial referencing. If the target table exists in the database and the value specified for Spatial SRID does not match the value contained in USER_SDO_GEOM_METADATA, the metadata's SRID will be used in place of the specified SRID.

If the SRID is not specified, tables will be created with a null value for the SRID field.

Indicates which columns' values come from sequences. The syntax for this parameter’s value is of the form:

column1:seqname1;column2:seqname2

Where "columnN" is the name of the column whose value is provided by the sequence, and "seqnameN" is the name of the sequence in the Oracle database.

As a concrete example, the value of this parameter might be:

id:mySchema.test_sequence

Where id is also specified under the User Attributes tab. mySchema.test_sequence refers to a sequence named test_sequence in the schema mySchema.

If ":seqnameN" is not given, the column's value will be provided by a sequence with the same name as the column. Sequence names are case-sensitive. The sequences will be created if they do not already exist, in which case a message will be written to the log file.

Specifies a custom SQL query to be used to process features. This parameter is normally used for INSERT, UPDATE, and DELETE operations that require custom SQL support, such as exception handling. The values in the query can be bound by embedding ”:attrName” in the query itself, where attrName is the name of the FME feature’s attribute. For example:

INSERT INTO EXAMPLE VALUES (:a, :b)

In this example, the attributes named a and b will be taken from each feature written to the table. The attributes named in the query must be listed in the User Attributes tab.

As another example, this UPDATE query:

UPDATE RR SET TEXTSTRING=:mytext WHERE ID=:myid

In this example, the Oracle column named ID is compared to the value of each feature’s attribute named myid, and the value of the table’s column named TEXTSTRING is set from the feature attribute named mytext. Attribute/bind names must be consistently quoted (:”myid”) or unquoted (:myid). Quoting does not impact how feature attributes are matched to bind names. If two FME attribute names differ only by case, quoting of bind names will be required to allow both values to be written.

This parameter is encoded as described in Substituting Strings in Mapping Files.