Oracle Non-Spatial Writer Parameters

This parameter specifies a database connection for the reader through a drop-down menu. The Add Database Connection option in the menu allows you to create a new connection. The connection requires a name that describes the connection.

Note: The following characters are not allowed in connection names: ^ \ / : * ? " < > | & = ' + % #

The new connection can be made visible only to the current user, or can be shared among multiple users.

Specifies the Oracle database to connect to. This may be a Net Service Name defined in tnsnames.ora or an Easy Connection Identifier. Either form of connection identifier may be prefixed with username and password information.

[<username>[/<password>]@][//]<hostname>[:<port>]/<service_name>.

[<username>[/<password>]@]<net_service_name>

The username and password.

The name of the Oracle Workspace Manager workspace that will be used by the writer. All tables will be written using the same workspace. If this parameter is omitted, or left blank, the default LIVE workspace will be used.

If this parameter is selected (default), the Oracle database connection remains open for other requestors. A persistent connection is useful for workspaces that are long-running, or published using FME Server.

If this parameter is deselected, the connection to the Oracle database is closed as soon as possible after data processing is complete.

By setting this parameter to Yes, FME automatically modifies column names so they are safe for nonquoted use. In particular, a value of Yes tells the writer to automatically make column names uppercase, and disallow characters in the column names that require quoting within Oracle.

This parameter is only applicable when generating a writer.

This parameter specifies the transaction number of the last successful transaction. When loading data for the first time, set this value to 0.

This parameter tells the writer when to start actually writing features into the database. The writer does not write any features until the incoming feature belongs to a transaction whose transaction number is one past the specified transaction number.

Specifying a value of 0 (which is the usual setting) causes every feature to be output. A non-zero value is only specified when a data load operation is being resumed after failing partway through.

This parameter is useful for resuming writing after a transaction fails. The log will print a message similar to this:

Translation aborted – rerun by setting the writer parameter “Transaction to Start Writing At” to n

where n is the transaction number. Transaction numbers are an integer sequence (0, 1, 2, 3, etc.). All features written in the transaction whose number is logged will have been rolled back.

At this point, you might discover an Oracle server access issue or an issue in the input features, such as features that violate the primary key constraint of the destination table. If you can resolve the issue without changing the ordering of input features (for example, restore access to Oracle server, or regenerate the primary key attribute values on the features), you can then rerun translation by following the instruction in the log message.

During the rerun, all features in transactions prior to the transaction whose number is logged are processed by the translation, enter into the writer, and then become ignored. Features belonging to transactions whose number is equal to or greater than the transaction number logged will be written to the destination table.

Note: If this parameter is set to 0, then all features are written.

This parameter, also known as chunk size, specifies how many features the writer transmits over the network at a time to the database. Features will get cached in memory until this feature count is reached, and then they will be transmitted as a single chunk to the database.

Note that the bulk write feature count is at the writer level and not the feature type level.

This parameter has a significant impact on performance. When the database server is physically remote, round-trip time for data transmission can cause bottlenecks in data loading. To reduce network round-trip time, increase the value of this parameter.

This parameter specifies how many features are written before they are committed.

Note that the written feature count is at the writer level and not the feature type level.

• If this parameter is set to a small number: when a transaction fails, only a small number of features (e.g., all features in that transaction) get rolled back.
• If this parameter is set to a large number: when a transaction fails, a large number of features get rolled back.

This parameter allows for the execution of SQL statements before opening a table for writing. For example, it may be necessary to drop a constraint before attempting to write to it. The statements will be executed only when the first feature arrives at the writer.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

This parameter allows for the execution of SQL statements after a set of tables has been written. For example, it may be necessary to clean up a temporary view after creating it.

Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.

For example:

FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people
WHERE LastName='Doe' AND FirstName='John'

Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.

Any errors occurring during the execution of these SQL statements will normally terminate the reader or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

This parameter specifies how the writer proceeds when a problem arises while converting a value from one of a feature’s attributes to an Oracle column value.

Examples of such problems would be the truncation of a string value to fit into the target character column, an error in writing a non-numeric attribute to a numeric column, or an error converting an FME geometry into an SDO_GEOMETRY value.

• Yes: The conversion errors result in warnings. Features are logged and dropped from the translation.
• No: The writer will silently write null values or truncated strings when conversion errors occur.
• Warn: The writer warns and writes null values or truncated strings when conversion errors occur.