On this Page
|Table of Contents|
This Snap executes a SQL Merge with given properties. The documents that are provided in the input view will be merged into the provided table on the provided database. Each document on the input view is expected to be merged or created with/as a new record in the table.
Expected upstream Snaps: The columns of the selected table need to be mapped upstream using a Mapper Snap. The Mapper Snap will provide the target schema, which reflects the schema of the table that is selected for the Merge Snap.
Expected downstream Snaps: The Snap will output one document for every record merged, hence any document processing Snap can be used downstream.
Expected input: Document that conforms to the input view schema of the Snap. The input view schema is provided to an upstream Mapper Snap based on the selected service object.
Expected output: Document which represents the status of the merge operation for that document. Each merge operation is represented as one document in the output view.
A good example for a merge condition is SALARY = $SALARY (here we use the SALARY variable of the input document).
A bad example for a merge condition is SALARY = '100000' (this will not work since we validate the provided columns in the condition against the types defined in the table schema). You do not want to use the expression toggle on the merge condition, there is support for it but it requires undocumented functions. The bad example can be rewritten by using an upstream Mapper Snap, where '10000' is mapped to the SALARY variable, which then can be used in the merge condition as defined in a good example. The same applies for pipeline parameters, which need to be mapped upstream similarly using a Mapper Snap.
|Support and limitations:|
Works in Ultra Task Pipelines.
Required. The name for the Snap. You can modify this to be more specific, especially if you have more than one of the same Snap in your pipeline.
The database schema name. In case it is not defined, then the suggestion for the Table Name will retrieve all tables names of all schemas. The property is suggestible and will retrieve available database schemas during suggest values.
Required. Name of table to execute insert on
Default value: [None]
Required. The ON condition of the merge statement. A boolean-type expression is expected. For an explanation of merge syntax, see Oracle Merge.
Default value: [None]
|Number of retries|
Specifies the maximum number of attempts to be made to receive a response. The request is terminated if the attempts do not result in a response.
Default value: 0
|Retry interval (seconds)|
Specifies the time interval between two successive retry requests. A retry happens only when the previous attempt resulted in an exception.
Default value: 1
Following are two examples. The first one using a conditional update of one of the values which gets then merged into the data using a Oracle Merge Snap. The second one will showcase error handling.
In the first example, we will drop a table, insert data into a newly created table, update one of records and then merge that update result into the same table.
In the second example, we use an error view on the Oracle Merge Snap.