Oracle Reader Parameters

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 to access the Oracle database.

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

If this parameter is checked (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 Flow.

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

If checked, the Tables parameter (in Reader Parameters and Reader Feature Type Parameters), and the Merge Filter checkbox in the Reader Feature Type Parameters dialog will include tables that live in system schemas, or contain special characters like / and $.

If unchecked, Tables and Merge Filter will not include system tables or tables with special characters.

Specifies whether to keep the table qualifier. The full name of a table in a database is of the format:

<prefix>.<table_name>

Depending on the database format, the prefix can be <database_name>.<owner_name>, <owner_name>, or <schema_name>.

Checking this parameter box indicates that the reader should return the table name without any prefixes. This is useful, for example, when creating a workspace that will be passed on to another organization using the same table names, or performing a translation to another database format but with a different username.

When this parameter is selected during workspace generation, the source feature types will be the table names without any prefix; otherwise, they will contain the owner name as a prefix. It is recommended that you do not change this parameter after generating the workspace, because it is possible for no features to be successfully passed onto the writer (since the writer is expecting feature types with different names).

After specifying the database connection, click the Browse button (...) to select tables for import. A connection window appears while the system retrieves the tables from the database.

Once the Select Tables dialog appears, you can select one or more tables. Click OK to dismiss the window and add the selected table name(s) to the Tables parameter.

The WHERE clause parameter is used to constrain the row selection in tables chosen in the Tables parameter. FME will select only the rows (records) that match this condition.

The easiest method to construct a WHERE clause is by using the editor. Click the browse button (...) to open the editor, and use the SQL functions to construct the clause.

You can also type a WHERE clause directly in the parameter field:

If the WHERE clause SQL is invalid, the translation will fail.

Tip  For assistance with constructing WHERE clauses, click the AI Assist button in the WHERE Clause editor.

Use this parameter to expose Format Attributes in FME Workbench when you create a workspace:

• In a dynamic scenario, it means these attributes can be passed to the output dataset at runtime.
• In a non-dynamic scenario, this parameter allows you to expose additional attributes on multiple feature types. Click the browse button to view the available format attributes (which are different for each format) for the reader.

Coordinate systems may be extracted from input feature data sources, may come predefined with FME, or may be user-defined. FME allows different output and input coordinate systems, and performs the required coordinate conversions when necessary.

For systems that know their coordinate system, the Coordinate System field will display Read from Source and FME will read the coordinate system from the source dataset. For most other input sources, the field will display Unknown (which simply means that FME will use default values). In most cases, the default value is all you'll need to perform the translation.

You can always choose to override the defaults and choose a new coordinate system. Select More Coordinate Systems from the drop-down menu to open the Coordinate System Gallery.

Changing a Reprojection

To perform a reprojection, FME typically uses the CS-MAP reprojection engine, which includes definitions for thousands of coordinate systems, with a large variety of projections, datums, ellipsoids, and units. However, GIS applications have slightly different algorithms for reprojecting data between different coordinate systems. To ensure that the data FME writes matches exactly to your existing data, you can use the reprojection engine from a different application.

To change the reprojection engine, Select Workspace Parameters > Spatial > Reprojection Engine. In the example shown, you can select Esri (but the selection here depends on your installed applications):

Controls whether 3D polygons are read as 3D face geometries or as regular polygons.

Checking this box allows the reader to parse MapInfo style and index columns.

This optional generation parameter specifies the name of the MapInfo symbology style column. If a column by this name is found, it will be omitted from the schema of the generated source feature type, and the parameter will be set to the name of this column.

This optional generation parameter specifies the name of the MapInfo symbology index column. If a column by this name is found, it will be omitted from the schema of the generated source feature type.

A search envelope (also known as a bounding box) is a rectangular area that defines a geographic area. In FME, the easiest way to define a search envelope is to use search envelope parameters.

Defining a search envelope is the most efficient method of selecting an area of interest because FME will read only the data that is necessary – it does not have to read an entire dataset. Search Envelope parameters apply to both vector and raster datasets and can be particularly efficient if the source format has a spatial index.

Most FME readers have parameters to define the search envelope of data that is being read:

The parameters include the x and y coordinates of the bounding box as well as a parameter that defines the coordinate system.

How to Define the Bounding Box

Using the minimum and maximum x and y parameters, define a bounding box that will be used to filter the input features. Only features that intersect with the bounding box are returned. Note that the bounding box intersection is not a full geometry intersection (based on spatial relationships) that would be returned by a transformer like the SpatialFilter.

This parameter specifies the type of relationship that must exist between the search envelope and the geometry in the target layer. Any supported relationship, or combination of relationships, may be specified.

This table lists the valid geometry interaction relationships.

In addition to specifying a single relationship, one may specify a combination of relationships to be tested by concatenating them with a plus sign (+). For example, the parameter may be specified as INSIDE + TOUCH.

Specifies the test that is applied to the results of the above geometry relationship comparison. When using the object model, spatial queries return results of TRUE rather than the name of the interaction – as they do with the older relational model – so the default test for the object model is = ‘TRUE’, regardless of the type of interaction involved.

Rows are read from the Oracle database using a bulk reading technique to maximize performance. This parameter allows you to tune the performance of the reader by specifying how many rows are read from the database at a time.

The default is 200.

This parameter allows for the execution of SQL statements before opening a table for reading. For example, it may be necessary to create a temporary view before attempting to read from it.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor help.

Tip  If you have enabled FME AI Assist, you can get help creating SQL queries. Click the AI Assist button to open the editor.

Available menu options depend on the format.

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 read. For example, it may be necessary to clean up a temporary view after creating it.

For detailed information about SQL functions, click the corresponding menu item in the SQL to Run editor help.

Tip  If you have enabled FME AI Assist, you can get help creating SQL queries. Click the AI Assist button to open the editor.

Available menu options depend on the format.

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.

If this parameter is set to Yes, feature geometry will be read into an aggregate.

A parameter is set on the aggregate to indicate that each part of the aggregate is independent from the others, and its own geometry. Geometry parts of the aggregate are named and contain geometry according to their respective column in the table being read.

If a geometry is read as null, it will be appended to the aggregate as a null geometry. Geometry-specific data normally added to the feature as attributes will be added as traits to the geometry instead.