Oracle Writer Feature Type Parameters
|
To access feature type parameters, click the gear icon Tip To always display the editor in FME Workbench, you can select View > Windows > Parameter Editor.
General All feature types share similar General parameters, which may include Feature Type Name, Reader or Writer information, and Geometry. In most Writer Feature Type parameter dialogs, you can also control Dynamic Schema Definitions. Some database formats accept Table or Index Qualifier prefixes on the output table feature type. |
Action
This parameter specifies how features will be written into the destination table. Supported feature operations are described below. Note that the described behavior can be dependent on the selected options, as well as the underlying table properties.
- More information about Feature Operations.
|
Option |
Description |
If the Row Does Not Exist |
If the Row Exists |
|
Insert |
The writer appends a new row to a table using input feature attributes. |
The writer creates a new row using input feature attributes. |
Not always applicable: if the table does not have a unique key or it has an automatically generated unique key, insertion is always possible. The database cannot violate its key constraints; therefore, errors can occur on row insertion. For example, if there is a unique key and a user specifies the value with the feature, and the feature already exists, then FME Workbench logs an error. This error might be in the form of a rejected feature, or the database may stop processing altogether. |
|
Update |
The writer updates existing row(s) in a table using input feature attributes. A selection method must be specified in the Row Selection group. |
The writer rejects the input feature or logs an error if it is unable to continue. |
The writer only changes values of the existing row(s) corresponding to the input feature that differ from the input feature. |
|
Upsert |
The writer appends a new row or updates an existing row(s) using input feature attributes and/or geometries. A selection method must be specified in the Row Selection group. |
The writer creates a new row using input feature attributes and/or geometries. |
The writer changes values of the existing row(s) corresponding to the input feature that differ from the input feature. It leaves other attributes untouched. |
|
Delete |
The writer deletes an existing row(s) in a table. A selection method must be specified in the Row Selection group. |
The writer rejects the input feature or logs an error if it is unable to continue. |
The writer deletes existing row(s) corresponding to the input feature. |
|
fme_db_ |
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 according to the steps below. Note The processing steps listed below depend on a format's available Feature Operation options.
Note about earlier FME versions: To use fme_db_operation, you must set Feature Operation to this option. In previous versions of FME, you could set fme_db_operation when the destination feature type was set to Insert, Update, Upsert, or Delete. Doing this now will cause feature rejection. |
The action depends on the operation; however, in general, if nothing is specified, the value is treated as an Insert. | The value is treated as an Insert. |
Controls how the feature type handles destination tables:
- Use Existing – Write to an existing table If the destination table does not exist, the translation will fail.
- Create If Needed – Create the destination table if it does not exist.
- Drop and Create – (This option is not available in all formats.) Drop the destination table if it exists, and then create it. The writer will drop and re-create the table before writing any features to it. Tables will be overwritten when the first input feature is processed. If no features are sent to a feature type, then the corresponding table will not be overwritten.
- Truncate Existing – (This option is not available in all formats.) If the destination table does not exist, the translation will fail. Otherwise, delete all rows from the existing table.
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.
|
Feature Operation Option |
Row Selection |
|---|---|
|
Insert |
Row Selection is ignored. |
|
Update, Upsert, Delete (Options may differ depending on a format's available Feature Operation options.) |
A Row Selection condition needs to be specified for selecting which rows to operate on. |
This parameter group offers two methods to construct the selection condition:
Match Columns
The columns specified in the corresponding column picker dialog will be used for matching destination rows. All matching rows will be selected for update, upsert, or delete. If any feature attributes corresponding to the specified match columns contain null or missing values, the feature will be rejected.
WHERE Clause
This parameter opens a WHERE Clause Builder. You can also type a WHERE clause inline, without launching the Builder. It is optional to start the clause with the word 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, the 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.
Table Creation
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’;
Minimum X Ordinate
The minimum X ordinate value expected in the dataset.
Default: -180
If any X 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.
Minimum Y Ordinate
The minimum Y ordinate value expected in the dataset.
If any Y 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.
Default: -90
Maximum X Ordinate
The maximum X ordinate value expected in the dataset.
If any X values are present that are greater than this value, the data will still be written. However, querying the spatial index will produce undefined results.
Maximum Y Ordinate
The maximum Y ordinate value expected in the dataset.
If any Y values are present that are greater than this value, the data will still be written. However, querying the spatial index will produce undefined results.
Comparison Tolerance for X Ordinate
Specifies the comparison tolerance for the X ordinate value.
Values that are closer than this value are considered equal.
Contains Z Ordinate
Specifies whether the dataset includes Z values.
Contains Measures
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 Z Ordinate
The minimum Z ordinate value expected in the dataset.
Maximum Z Ordinate
The maximum Z ordinate value expected in the dataset.
Comparison Tolerance for Z Ordinate
Specifies the comparison tolerance for the Z values.
Values that are closer than this value are considered equal.
Default: 0.05.
Minimum Measure Value
The minimum measure value expected in the dataset.
Maximum Measure Value
The maximum measure value expected in the dataset.
Comparison Tolerance for Measures
Specifies the comparison tolerance for measures.
Values that are closer than this value are considered equal.
Default: 0.05.
Spatial
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.
Create Spatial Index
Indicates whether or not spatial indexes are to be created as part of the data load.
Drop Existing Index
Specifies whether to drop the existing spatial index for the table.
- Yes – The existing spatial index for the table will be dropped. A new spatial index will then be created.
- No – The table being written already contains an index, so no spatial index will be created.
Spatial Index Name
The name for the spatial index that will be created on the table. If this parameter is not specified, a spatial index name will be created based on a database sequence and the name of the table being written.
Quadtree Levels (0 for R-Tree)
The tessellation level used to create the spatial index for the layer. The larger the number, the longer spatial index creation will take by the finer the granularity of the index.
The range is any integer between 1 and 64. The default is 0 is given, so that no levels will be specified when the index is created. Depending on the version of the Oracle Spatial database being written to, this may force it to use RTree indexing instead of fixed or hybrid indexing.
If using fixed or hybrid indexing, a positive integral value must be specified.
Hybrid Index Numtiles (0 for Fixed Index)
The number of variable-sized tiles used (per geometry) when creating a hybrid spatial index within the object model. The range is any positive integer.
If this parameter is not specified at the time when a spatial index is created, and Quadtree Levels oracle_levels is specified, fixed spatial indexing will be used in the created index.
Advanced
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 process features. This parameter is usually used for Insert, Update, and Delete operations that require custom SQL logic, such as exception handling. The values in the query can be bound by embedding :attrName in the query, where attrName is the name of the FME feature’s attribute. For example:
INSERT INTO "TEST_TABLE" ("ID", "GEOM") VALUES (:KEY, :MYGEOM)
In this example, the attribute named KEY will be taken from each feature written into the table column named “ID”. The geometry on the feature will be written into the spatial column named “GEOM”. The attributes bound in the query, including the spatial "attribute" named MYGEOM, must be listed in the User Attributes tab. The User Attribute types should align with the column types in the table schema, although for the spatial "attribute", any User Attribute type may be specified. Further, the spatial "attribute" named MYGEOM must be specified in the format user attributes Spatial Definition table:
As another example, consider this UPDATE query:
UPDATE "TEST_TABLE" SET "GEOM"=:MYGEOM WHERE "ID"=:MYID
In this example, the attribute named MYID from each feature is matched against the column “ID”. Matching rows will have their “GEOM” column updated. The spatial column “GEOM” gets its value from the feature’s geometry. Once again, MYGEOM needs to be specified in the format user attributes Spatial Definition table.
This must be true for a spatial “attribute”. For non-spatial attributes, if the pairing is identical except for letter case, the User Attribute name will prevail, unless an attribute with that name does not exist on the feature; in this case, the bound attribute name will prevail. If the bound attribute name also does not exist on the feature, the value of the nonexistent attribute should default to null.
Because of this override behavior, when there are multiple attributes on the feature with identical names except for letter case and you want to bind an attribute whose name is not all uppercase, you may need to adjust the Case Conversion options in the writer parameters when you add the writer.
Custom SQL Binding
Features may also be read through the SQLCreator or SQLExecutor transformers using bound variables.
Before the query, users may specify an FME bind directive with space-delimited name-type pairs. These bind directives begin with FME_BINDS_BEGIN and end with FME_BINDS_END.
For instance, if a user wishes to get data out of the table TEST and TEST2 using PLSQL, they can write the following in the custom SQLExecutor/SQLCreator prompt:
FME_SQL_DELIMITER +
FME_BINDS_BEGIN
CURS1 RESULT_SET
CURS2 RESULT_SET
FME_BINDS_END
BEGIN
OPEN :CURS1 FOR SELECT * FROM TEST1;
OPEN :CURS2 FOR SELECT * FROM TEST2;
END;
The result port will output features in the order the users specified in the bind directive. In this case TEST1 will be read, and then TEST2 will be read. This may also be used to receive output data from a stored procedure. Values may be passed through FME attributes into bind variables.
FME_SQL_DELIMITER +
FME_BINDS_BEGIN
HELLO VARCHAR(300)
TEST VARCHAR(300)
LINES RESULT_SET
POLYS RESULT_SET
FME_BINDS_END
BEGIN
:HELLO := 'HELLOWORLD';
:TEST := 'This is a test';
OPEN :POLYS FOR SELECT * FROM FMEENGINE_71210_POLYS;
OPEN :LINES FOR SELECT * FROM FMEENGINE_71210_LINES;
END;
This example will first output a feature for all the scalar valued binds. It will then output all the records of the result sets in the order their variables were declared. In this case, the features for LINES will be read with the oracle_cursor attribute of each feature set to LINES, then the features for the POLYS attribute will be read with the oracle_cursor attribute of each feature set to POLYS.
The currently supported types for Oracle binding are as follows:
|
Name |
The name of the column to be written. |
|
Type |
BLOB CHAR CLOB DATE FLOAT INT INTEGER LONG NCHAR NCLOB NUMBER NVARCHAR NVARCHAR2 REAL RESULT_SET SHORT SMALLINT STRING TIMESTAMP VARCHAR VARCHAR2 |
on a feature type in the